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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

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.
 
Upvote 0

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?
 
Upvote 0

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
 
Upvote 0

Forum statistics

Threads
1,186,916
Messages
5,960,554
Members
438,484
Latest member
supermon

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