Conditional formatting comparing two columns

Shelina · External Since: Nov 05, 2009 Posts: 3

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!
--
Shelina

Jacob Skaria · External Since: Mar 04, 2009 Posts: 441

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
---------------
Jacob Skaria

"Shelina" wrote:

> 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!
> --
> Shelina

Peo Sjoblom · External Since: Sep 14, 2009 Posts: 10

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" <Shelina.DeleteThis@discussions.microsoft.com> wrote in message
news:4F9384B6-F9BE-4ED8-81CB-0F681B15319F@microsoft.com...
> 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!
> --
> Shelina

Shelina · External Since: Nov 05, 2009 Posts: 3

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?
--
Shelina

"Jacob Skaria" wrote:

> 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
> ---------------
> Jacob Skaria
>
>
> "Shelina" wrote:
>
> > 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!
> > --
> > Shelina

Jacob Skaria · External Since: Mar 04, 2009 Posts: 441

--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
---------------
Jacob Skaria

"Shelina" wrote:

> 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?
> --
> Shelina
>
>
> "Jacob Skaria" wrote:
>
> > 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
> > ---------------
> > Jacob Skaria
> >
> >
> > "Shelina" wrote:
> >
> > > 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!
> > > --
> > > Shelina

Shelina · External Since: Nov 05, 2009 Posts: 3

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.
--
Shelina

"Jacob Skaria" wrote:

> --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
> ---------------
> Jacob Skaria
>
>
> "Shelina" wrote:
>
> > 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?
> > --
> > Shelina
> >
> >
> > "Jacob Skaria" wrote:
> >
> > > 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
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "Shelina" wrote:
> > >
> > > > 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!
> > > > --
> > > > Shelina

Jacob Skaria · External Since: Mar 04, 2009 Posts: 441

Try out in a new workbook

If this post helps click Yes
---------------
Jacob Skaria

"Shelina" wrote:

> 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.
> --
> Shelina
>
>
> "Jacob Skaria" wrote:
>
> > --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
> > ---------------
> > Jacob Skaria
> >
> >
> > "Shelina" wrote:
> >
> > > 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?
> > > --
> > > Shelina
> > >
> > >
> > > "Jacob Skaria" wrote:
> > >
> > > > 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
> > > > ---------------
> > > > Jacob Skaria
> > > >
> > > >
> > > > "Shelina" wrote:
> > > >
> > > > > 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!
> > > > > --
> > > > > Shelina