Fill entire row with color if one cell contains certain text

[KarlaB]

Forgive me if this is answered elsewhere, but I am trying to use Excel 2007's conditional formatting to fill an entire row with color if one cell in that row contains specific text. (I will need about 10 colors.)

For example, I'd want the rows colored differently based on department

First Last Dept. Ext.
Joe Smith Accounting X4827
Ann Gable Purchasing X4888
Karla Barnett Operations X4000
Dave Brown Finance X2000



I am asking if there is an IF statement with a lookup table or if I can write a new rule in Excel 2007's conditional formatting on the Ribbon. (I don't see an option to highlight an entire row based on the contents of one cell in that row. If so, can you tell me the exact rule or statement I would need to write? Thank you much!

[JBeaucaire]

Assuming the columns you want to format are A:Z
Assuming the department is in column C starting at C2

1) Highlight A2:Z100 (or however many rows you want this)
2) Create a new CF rule with the following formula:

=$C2="Accounting"
...and pick your FILL color.

3) Repeat step #2 for each department/color combination.

[KarlaB]

Thank you, JBeaucaire. I tried it and it worked! Now, I am wondering is there a symbol in Excel that I can use to specify that a cell contains certain text rather than has only that text? (For example, sometimes the cell might say "Providence Room" and other times it might say "PROV," so I would want my formulat to say =C2[some symbol here for CONTAINS, not equals] PROV...and pick your FILL color.

I am so happy I could understand your answer!

[JBeaucaire]

CF formulas are true/false contructs. You play with these formula in regular cells until you get true/false as expected. Then you move them into the CF window. Try this:

=ISNUMBER(SEARCH("Prov", A2"))