Filling a cell with using conditional formatting in two other cells

[Jouko305]

Hi,

I've been having some hard time figuring out how to solve this.

I have three columns with actual data (numbers) and would like to illustrate conclusions based on the first and third cell into a fourth cell in colors.

Let's say that my borderline is 4 and all numbers higher that that should first turn the respective cell (1st and 3rd) into red and at the same time turn the fourth summary cell red as well. 

This works well for the first row , but I need 70 rows with the same logic.  When I copy this described logic for remaining 69 rows, it blows out: the value inserted into first row turns other rows red as well. Some how I would need to define that this color logic is depended into the precise row.

Any ideas how to do it ?

cheers, jouko

[#2 · Accepted solution]

@Jouko305 

You can try the attached file where i've entered two rules for conditional formatting. Maybe this is what you want to do.

[#3]

@OliverScheurich 

You saved my day !

cheers, jouko

[#4]

@Jouko305 

I think you need three rules for conditional formatting based on formulas - one for each column. Note that in each rule the range to apply the rule is an absolute reference whereas the formula uses the first linenumber without "$". This iterates the formula over the range.