Conditional formatting if cell contains certain words

J

Jay

I'm trying to change the fill color of a cell if the cell contains a certain word

Example: The key word is "sky

Cell A1 contains: "The Sky is blue
Cell A2 contains: "The sun is yellow
Cell A3 contains: "The sky has clouds

So, I'd like cells A1 and A3 to be filled with yellow since they contain the word "sky

Is this possible

Thanks for any help.
 
K

Kevin Stecyk

Hi Jay,

Here is one way...

Use Format | Conditional Formattting with the following formula...

=NOT(ISERROR(FIND("sky",A1))) for cell A1....similar for cells A2, and A3

Regards,
Kevin
 
D

Debra Dalgleish

1. Select cells A1:A3
2. Choose Format>Conditional Formatting
3. From the first dropdown, choose Formula Is
4. In the formula box, enter a formula that refers to the active cell:
=ISNUMBER(SEARCH("sky",A1))
5. Click the Format button, and choose yellow on the patterns tab.
6. Click OK, click OK
 
K

Kevin Stecyk

Hi Jay,

Glad it worked. If you are still checking in, you should view Debra's
solution. I like her solution better because it uses SEARCH which is not
case sensitive and her method of checking to see if sky exists is better
than my iserror solution which tends to be very generic. Her solution
appears more specific to isnumber.

Regards,
Kevin
 
J

Jay

Thanks, Debra -

I actually was having some problems with case so your method was the way to go...

Jay
 
Top