Conditional Formatting Driving Me Crazy

[Cactus1993]

If Cell A1 = 5, and Cell B1 is greater than Cell A1, I'd like the fill color
in B1 to be red. If Cell B1 is less than or equal to Cell A1, I'd like it to
be green.

Now the part I'm frustrated with in Excel 2007. In Excel 2003, I could drag
the conditional formatting down. Now I can't figure out how to:

If Cell A2 = 20, and Cell B2 is greater than Cell A2, I'd like the fill
color in B2 to be red. If Cell B2 is less than or equal to Cell A2, I'd like
it to be green.

I can't seem to "drag" the conditional format from Cell B1 to Cell B2 -- I'm
applying the same basic conditional format, but need B2 to reference A2, and
not A1 as it automatically seems to be doing. The conditional formatting rule
always refers to Cell A1's value, even when I write it as =$A1 (as opposed to
=$A$1).

Is there any way I can "drag" conditional formatting rules to increment the
reference cell, and not have to individually conditinal format every cell in
the huge column I'm trying to format?

Thanks much, in advance!

 

[Chin via OfficeKB.com]

Hi, I try your example in my Excel 2007 and it works fine (I can drag without
encountering the problem you describe). Just a quick check, did you click on
"Apply" after you change the reference to cell =$A1?

 

[T. Valko]

You can apply the formatting all at one time.

Let's assume you want to apply the formatting to the range B1:B25

I'm assuming no cells are empty.

Select the entire range, B1:B25
Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=B1>A1
Click the Format button
Select the Fill tab
Select a shade of RED
OK>OK
New Rule
Use a formula to determine which cells to format
Enter this formula in the box below:
=B1<=A1
Click the Format button
Select the Fill tab
Select a shade of GREEN
OK>OK>Apply>OK