Is there a quicker way to Conditional Format in Excel

[jrobin5881]

I have a number value in each cell C2:V2 I want to use conditional formatting to say that if the value in each cell is >= the value in B2 change the font to blue. It works fine using the conditional formating wizard off the tool bar, but I have over 200 rows and it's a pain going row by row.

The problem I have is when I highlight C2:V300, click the conditional format wizard it uses the criteria in cell b2 for the entire range. When I try to change the criteria to B2:B300 I get a message that says I can't use a range in conditional formatting.

 

[AnotherHiggins]

Highlight the entire range and do the conditional formatting once. Just place dollar signs in front of the B and the 2, i.e. >= the value in $B$2.

That tells Excel to use absolute references instead of relative references, as it is now.

You can toggle through reference styles by clicking in the cell reference (B2) and clicking <F4> to cycle through relative ref, absolute ref, relative col absolute row, and absolute col relative row.

[tt]_____
-John
[/tt]Help us help you. Please read FAQ181-2886 before posting.

 

[jrobin5881]

My apologies - I want it to be relative so Row 2 would compare the values from C2:V2 to B2, Row 3 would compare the values in C3:V3 to B3 etc.

 

[SkipVought]

Select the entire range (C2:V300)
[tt]
=$B2<=B2
[/tt]


Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[byundt]

I think Skip had a typo. He probably meant to say:
1) Select cells C2:V300
2) Open the Format...Conditional Formatting menu item
3) Choose "Formula Is"
=C2>=$B2
4) Click the Format button, then choose your blue font color
5) Click OK

If it doesn't work, repeat steps 1-3 and make sure Excel didn't add $ or "" in its zeal to be helpful.

Brad

 

[SkipVought]

You're correct, Brad...
[tt]
=$B2<=C2
[/tt]


Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[AnotherHiggins]

Not that it really makes a difference, but why bother with 'Formula is"?

Just go with "Cell value is"
-in the second box, put in greater than or equal to
-in the final box, put in [COLOR=blue white]=$B2[/color]

Or, for the last step, you can click on cell B2 then press <F4> to toggle through reference options as described in my previous post.


[tt]_____
-John
[/tt]Help us help you. Please read FAQ181-2886 before posting.