conditional formatting

[lauren18]

Please help! I am so stuck!!

I need to format several columns and rows with the following rules but I can't get all cells in my selection to change colour. For some reason only Q5 is changing to red

Also, I need to automatically duplicate this formatting on other rows without having to manually input the formatting rules for each row.

The rules I need are as follows;

If L5>M5
cells K5:R5 need to be red

If L5<M5
cells K5:R5 need to be green

If L5=$0.00
Cells K5:R5 need to be yellow

 

[pvr928]

Hi Lauren

In all cases when you're in the conditional formatting window make sure the range K5:R5 has been selected.

Case 1
Formula: =$L$5>$M$5
Formatting: Red

Case 2
Not sure what you mean

Case 3
Formula: =$L$5=0
Formatting: Yellow

If your conditional formatting is not working as expected, there is always a logical reason why. I always make sure if I'm having difficulty with CF I delete all the CF that is currently there and start from scratch.

Have fun

cheers

pvr928

 

[lauren18]

HI,

this hasn't worked. I've tried several times, clearing all conditional formatting from the spreadsheet.

using =$L$5>$K$5 (Red) hasn't applied to any of the cells

Where I've applied =$L$5<$K$5 (Green) - this has applied to all rows that do not have a 0

How do I apply all three conditions at once? and have them function correctly?

 

[pvr928]

Hi lauren18

If you're confident no other CF is being applied, then remember CF is based both on logic and hierarchy.

The CF rules operate in order of their 'seniority' so the first rule will override the second.

Also, look at the rules themselves - more than one can be true at any one time, ie L5 can both be zero and higher than M5.

I suggest you carefully set out what you are trying to achieve and then accept the limitations of these two principles, or modify your ranges (and so the CF) to suit your needs.

Have fun

cheers

pvr928

 

[lauren18]

Hi pvr928,

as 0 only appears in column L and all figures in column M must have a value of 1 or more zero cannot be higher than another figure. There are no minus figures. 0 is the lowest value in this instance.

I could go about this another way. I'm a third column, column N, there is a total of the difference between columns L and M.

i.e. M5 - L5 = N5

if N5 is a negative figure it must be red, if it is a positive figure it must be green.

Could this work better?

 

[lauren18]

Also this needs to apply to several rows, so I don't think the formulas were correct.

If L5 < M5 then row 5 should be red. If L6 > M6 then row six should be green etc.

How do I automate this without having to create a rule for each row?