Conditional Format Range of Cells

[tommcbrny]

Hi, I am using Excel 2007 and trying to apply conditional formatting as
follows:

If number in column B is greater than number in column D, format text in
column A
If number in column B is less than number in column D, format text in column
C.

So if B1 > C1, format A1, then if B2<C2, format D2, then if B3>C3, format
A3, and so on.

I am able to format text in cell A1 if the number in B1 is greater than C1.
I also figured out how to apply this format to all cells in column A, but
only based on values in B1 & C1.

Is there a way to specify the formual in conditional formatting so that it
compares row by row without entering a new conditional format rule for each
row?

Thank you,
Tom

 

[Jan Karel Pieterse]

Hi Tommcbrny,

Is there a way to specify the formual in conditional formatting so that it
compares row by row without entering a new conditional format rule for each
row?

Just select all cells you need the conditional format for and set the formula
up for the selected cell (the white-ish one) using cell references without $
signs in them where appropriate.

So if you are on row 2, set the formulas so they point to the cell(s) on row 2.
Excel will adjust themfor the other cells automatically just like it does when
you copy a formula.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Luke M]

When you are writing the rule, make sure the row reference is relative ($B3)
and not absolute ($B$3). This will allow the rule to adapt to each row you
copy it to. I'm afraid I don't use 2007 myself, or I would be clearer in my
guidance.

 

[tommcbrny]

I tried doing this, but notice in 2007 that it specifies an absolute
reference by default and goes back to the absolute reference even if I change
to a relative reference and apply the change. As soon as I click "Apply", it
reverts to the absolute reference.

Thank you for the suggestion though.

 

[tommcbrny]

Ah, never mind, I am able to specify the relative reference, that does the
trick. Thank you.