# Conditional formatting comparing two columns

#### Excel2006

##### Active Member
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
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
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
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

##### Active Member
lenze

That's been a great help, thx.

Replies
5
Views
71
Replies
5
Views
238
Replies
2
Views
70
Replies
12
Views
211
Replies
5
Views
255

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.

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.

### Which adblocker are you using?

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

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