Excel 2007 : Conditional Formatting With Formulas

[krt777]

I'm using excel 2007.

I have two columns of data. See attached for basic example.

If the value in column b is less than the value in column a, highlight the value in column b light blue.

If the value in column b is greater than the value in column a, highlight the value in column b light pink.

This rule has been applied to cell B1. You can look at this rule by manage rules.

I have not been able to find an easy way to apply the conditional formatting to cells B2 thru B5 in an easy way.

The odd behavior is that I can only use the format painter to one cell at a time. If I try to apply the format painter to more than one cell the formula references stay fixed like an absolute reference. I have only been able to do brute force and format paint one at a time. This will not work for lots of data or take too much time.

Does anyone have an easier way?

[rwgrietveld]

I work with 2003 but try this.

1) Select the complete column B:B and set CF to
a) =IF(B1>A1,1,0) Blue
b) =IF(B1>A1,1,0) Red

Excel automatically changes B1 -> B2 and A1 -> A2 etc

[krt777]

I have worked with 2003 and have had no trouble before. You just take away the absolute references "$" and it will work.

I'm looking for a way to make it easier in 2007.

[krt777]

Hi rwgrietveld:

I just tried changing the conditional formatting rule to the "IF" logic in B1 and then used the format painter to paint B2 thru B5.

Two rules in B1
=IF(B1>A1,1,0)
=IF(B1<A1,1,0)

This is what ended up in B2, which looks good.
=IF(B2>A2,1,0)
=IF(B2<A2,1,0)

This is what ended up in B3 thru B5, which looks bad. Its the same as what ended up in B2.

=IF(B2>A2,1,0)
=IF(B2<A2,1,0)

Ken

[krt777]

I might just be getting confused on how office 2007 displays the rules if you peak into any cell. If you peak into a cell you have to assume the reference has been incremented even if the rule mentioned is the the one applied to the first cell.

In 2003 if you looked that a cell it would should the rule incremented for that cell. It doesn't in 2007. It shoes the first rule applied in the copy throughout all and assumes you know if increments.

I don't think this is good in all cases since its nice to see the actual increment in case something in your formula shouldn't be incremented and needs to be made an absolute reference.

[rwgrietveld]

I do not fully understand your explination, but I think 2007 IS NOT different than 2003 in case of CF. If you look at the CF of B5 than this should have =IF(B5<A5,1,0) in it and not =IF(B2<A2,1,0).

This will not help you, but you might try different approaches.
Do the CF of B2 and drag down
Select the ENTIRE column B:B and add the CF

In my case all end up with the incrementation B1 ... B5.