Conditional formatting using a rule "Contains"

**alexlead** · 08-24-2011, 07:11 PM

Dear Conditional Formatting gurus, could someone please advise how could I do the following - I'd like to make a whole row Red, if a cell value in the Status column contians a specifice "text"

Could you please provide an example on how I could do that?

Thanks a lot in advance.

**tlafferty** · 08-24-2011, 07:16 PM

If your status column is the D column and your text you're looking for is the word "bogus", then use this formula in the conditional formatting rule:
=SEARCH("bogus",$D2,1)>0

Fill that format across and down as many rows/columns as needed.

**daddylonglegs** · 08-24-2011, 07:18 PM

If status column is column G then you can colour all of A2:Z2 by doing this:

Select A2:Z2,

use this formula in conditional formatting

=$G2="text"

Note the $, you need that

**tlafferty** · 08-24-2011, 07:34 PM

Hi Daddylonglegs - You're formula is shorter and easier to use. Maybe I misread the requirement. I thought the user was looking for testing whether any portion of the text string in the status column contained a certain word, rather than the cell value being exactly equal to the keyword. If the cell value needs to be exactly equal, then yours is the prefered method.

**alexlead** · 08-24-2011, 08:13 PM

Thanks a lot Thomas - your answer did help! I was looking for "testing whether any portion of the text string in the status column contained a certain word".
One question though: did you put "$D2,1" as a joke to test if I figure it out? "$D2,1" identifies the previous row; since I wanted to make Red the row that conatains the "text", but D2 makes Red the previous row, I used =SEARCH("bogus",$D1,1)>0 instead and it worked just fine!

Thanks again!