Excel – How to express “if column A is greater than column B, format column B”

conditional formattingmicrosoft-excel-2010

I'm having trouble with conditional formatting- so would really appreciate some guidance!

I want to format the numbers in column B, relative to the values in column A.

So if B is equal to or greater than A, format it green. If A is greater than B format it red.

(ahh i can't post an image!) Imagine for example:

Col A    Col B

  123     456

  324     290

   -50     130

 -210    -311

   123    123

Using the 'Greater Than' function i can achieve this on a row by row basis, but i can't figure out how to do this for a range of rows. So as in the example B1:B10 and A1:A10.

Similarly i can't combine the 'equal or greater than' with the 'less than' functions. But i guess that is simply a case of having two distinct rules that achieve the desired result.

Best Answer

Select the entire range (using your example, B1:B10).
Add a new conditional formatting rule.
Select Use a formula....

formatting rule view

the formula you use should be =(B1>=A1) (or =(B1<A1) for the red)
you will have to type this, as using arrows or clicking on the cells will insert a fixed reference with $ signs, which we have to avoid. Use the Format button to change the color (fill color for the example).

Now when you click OK, you will see the color change for the entire range based on the value next to them, rather than a fixed value you get when clicking on cells

Related Question