formatting based on a condition

[paulrus]

Can anyone give me an example of how to format a ROW based on a formula? For example - if cell E6= "FL" then shade the entire row 6 Green.

I've seen examples on formatting text, but not entire rows.

Thanks

Paul

 

[paulrus]

Duh - I need to read before I reply...
Thanks...

 

[Mark W.]

On 2002-04-22 08:09, paulrus wrote:
If I can't use an array, how do I format based on a list of states? I don't quite understand how what you wrote would work if Excel has no idea that I only want those specific states to be colored differently.

1. List your state abbreviations in column A of a new worksheet (e.g., Sheet2).
2. Select the entire row 6
3. Choose the Format | Conditional Formatting... menu command
4. Setup a "Formula Is" condition using =COUNTIF(Sheet2!A:A,$E6) and applying a green cell pattern.
This message was edited by Mark W. on 2002-04-22 08:15

 

[paulrus]

You can't use reference to other sheets using conditional formatting.... or at least that's what Excel is telling me...

 

[Mark W.]

On 2002-04-22 08:23, paulrus wrote:
You can't use reference to other sheets using conditional formatting.... or at least that's what Excel is telling me...

Ahh, that's probably true. I was trying to simplify my instructions. Just find some unused space on your worksheet, enter your state abbreviations there, and use that cell reference as the 1st argument to the COUNTIF function.

 

[paulrus]

I moved them to A70-84 and changed it to read:

=COUNTIF($A$70:$A$84,$E6)

However any row I apply this to changes color regardless of whether or not it matches A70-84.

 

[Mark W.]

On 2002-04-22 08:39, paulrus wrote:
I moved them to A70-84 and changed it to read:

=COUNTIF($A$70:$A$84,$E6)

However any row I apply this to changes color regardless of whether or not it matches A70-84.

I can't replicate your problem. Be sure that you're following these steps...

1. List your state abbreviations in A70:A84
2. Select the entire row 6
3. Choose the Format | Conditional Formatting... menu command
4. Setup a "Formula Is" condition using =COUNTIF($A$70:$A$84,$E6) and applying a green cell pattern.

 

[paulrus]

Interesting - I found a minor bug in Excel I think.

Basically I was trying to select multiple rows to accomplish the task. However, Excel formats the first row selected to whatever color you choose regardless of whether or not it matches the list.

I just selected the row above where my data starts and now the page formats correctly.

Thanks for the help!

Paul