Conditional formatting comparing two columns

Excel2006

Active Member
Joined
Dec 19, 2006
Messages
455
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

klb

Well-known Member
Joined
Apr 3, 2002
Messages
821
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

Active Member
Joined
Dec 19, 2006
Messages
455
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,095
Messages
5,768,054
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top