Conditional formatting comparing two columns

[Excel2006]

Hi

I've been trying to set up Conditional formatting comparing two columns.

e.g. If cell in column A is bigger than cell in Column B then colour cell in column (A) RED

If cell in column B is bigger than cell in Column A then colour cell in column (B) RED

I can do the action to compare two cells and try copying the formula down the lat row but it doesn't work.

Any help appreciated.

 

[klb]

Make sure that your conditional formating references do not contain the $

Then just use the format painter and paint the additional cells that you want to compare.

 

[Excel2006]

Thx klb.

Another scenario has cropped up.

If cell a1 > cell b1 then colour cell a1 "Blue" and copy down, which now works ok.

2) However, if cell a1 > cell b1 because cell b1 is empty, I want to colour the cell "green".
and vice versa

i.e. if cell b1 > cell a1 because cell a1 is empty, I want to colour the cell "green".

Using Conditional Formatting, how would I do this?

 

[lenze]

This should get you started.

For Column A
Condition 1, Formula Is
=(A1>B1)*(NOT(ISBLANK(B1)))
Format red

Condition 2, Formula Is
=(A1>B1)*(ISBLANK(B1))
Format green

HTH
lenze

 

[Excel2006]

lenze

That's been a great help, thx.