Conditional formatting based on result in another cell

Question

I want to color cell E6 based on results in cell F6. If F6 <0,002 then the color should be red - if F6 <0,005 then the color should be blue - if F6 =>0,005 the color should be green. 

Then I have a whole lot of results in the rows underneath with the same problem. How can I make a rule that does this easily colors cell E6 and all the cells in column E under....

Thanks if anyone can help...

All the best from Kathrine

Answer 1

Select the cells that you want to format, say E6:E100.

On the Home tab of the ribbon, click Conditional Formatting >Manage Rules...

Click New Rule...

Select the option 'Use a formula to determine which cells to format'.

Enter the formula   =F6>=0,005

Click Format...

Specify green as the desired font or fill color, then click OK twice.

Repeat the above steps, but now with formula  =F6<0,005   and blue.

Finally, repeat them again with   =F6<0.002   and red.

Back in the Conditional Formatting Rules Manager, tick the check box "Stop If true" for the rule at the top (the one for red), then click OK.

Answer 2

If you don't tick the check box "Stop if true", Excel will evaluate each rule in the list, starting at the top and working downwards, and apply each one for which the condition is true.

So for example, if F6 is 0,001, the first condition =F6<0,002 is true so E6 is colored red. But the second condition =F6<0,005 is true too so the cell is colored blue.

Ticking the check box "Stop if true" makes Excel stop evaluating the rules at the first condition that evaluates to True.

In the above example, since the first condition is True, the second condition won't be evaluated. So the cell remains red.

Answer 3

Thanks! Just a quick note - why must I tick the check box "Stop if true" - what does that actually do?