Conditional formatting comparing two columns

[Shelina]

Help! I need to create conditional formatting that highlights a cell, if the
value in column F is greater than the value in column B. How do I make that
work? Thanks!

 

[Jacob Skaria]

1. Select the cell/Range (say F1:F10). Please note that the cell reference F1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection..
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=F1>B1

'To handle blank entries you can try the below formula
=AND(COUNT(F1,B1)=2,F1>B1)

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

PS: If you are using Excel 2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format

If this post helps click Yes

 

[Peo Sjoblom]

Select the range in column F, assume the first select cell is F2, then apply
conditional formatting, use formula is, then =F2>B2, click the format
button, select patterns and choose high light colour then click OK Twice.
That would be for 2003 and earlier

For 2007 click the icon for cf, then select new rule, select use a formula
to determine which cells to format, put =F2>B2 in the rule description, then
format button, select fill, then colour. As usual it is more steps involved
in the idiotic 2007



--


Regards,


Peo Sjoblom

 

[Shelina]

Thanks for your help.

I am using 2007. I did as you suggested and random cells were highlighted,
non of them were examples of =F1>B1. I tested =B1>F1 and the resulting
highlighted cells were all the cells which were not highlighted in =F1>B1. Do
you have any idea what might be happening there?

 

[Jacob Skaria]

--Make sure your selection is Range F1:F10. Please note that the cell
reference F1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection..

--Copy paste the below formula
=AND(COUNT(F1,B1)=2,F1>B1)

--Enter 1 in cell B1 and 2 in cell F1 (f1 should be highlighted)
--Enter 2 in cell B2 and 3 in cell F2 (f2 should be highlighted)
--Enter 2 in cell B3 and 1 in cell F3 (f3 will not be highlighted)

If this post helps click Yes

 

[Shelina]

I have data validation going on in column B, which doesn't allow me to use
small numbers, so I added two columns to the table and did as you suggested.
The result was that all three cells were highlighted. Then, I added 4,5,6 to
three other cells in the first column (in this case, M) and 6,5,4 in the
adjoining cells in the second column (N). I used =N1>M1. The result was that
the, in column N, containing 5 was highlighted.

I think there is something wrong with the table. I am working on a class
assignment and the table was created by the teacher, so I don't know what
might be going on there.

 

[Jacob Skaria]

Try out in a new workbook

If this post helps click Yes