Conditional formatting: Highlight cells in a column that contain 1 or more words

Conditional formatting: Highlight cells in a column that contain 1 or more of a set of words.

Below: I want all cells in colum B that contain the words "CAT", "MONKEY", or "BULL" highlighted regardless of other punctuation or words in the cell.

I found the following formula but it does not work:
=IF(ISERROR(IF(LEN(B1)>0, FIND(","&LOWER(B1)&",", ",cat,monkey,bull,"),#VALUE!)),"=",B1)


Please see attached spreadsheet.

A B
1 CAT TIGER
2 MAN
3 CHICKEN, DONKEY854
4 DUCK" SNAKE
5 MAN
6 SNAKE, MONKEY 2658
7 MONKEY: TIGER
8 MAN(
9 DOG, BIRD 4564
10 BIRD, SNAKE
11 TIGER, DONKEY
12 MAN
13 LION, MONKEY
14 BULL, LION
15 DONKEY, TIGER
16 MAN
17 COW, SNAKE..4
18 PIG, COW
19 MAN
20 OWL, MONKEY

Notice some words are not separated by spaces or are followed by or preceeded by punctuation (if the word is in complete I want the cell to be highlighted regardless of what is before or after).

I am using Excel 2003, and was told the above formula should be entered in the cell vale/equal to....

Thank you,
Daniel

try the following in yout CF...

=NOT(AND(ISERROR(FIND($I$4,D9,1)),ISERROR(FIND($I$5,D9,1)),ISERROR(FIND($I$6,D9,1))))

to give you more flexibility, i put CAT, MONKEY and BULL in seperate cells - I4, I5 and I6. you can replace them in the formula if you want

What about this in conditional formatting with your list in some cells somewhere named List

=SUMPRODUCT(--(ISNUMBER(SEARCH(List,A4))))>0

and the cells to be highlighted starting in A4

Does this sample help with both suggestions?

the reason for the NOT was because i had to use error trapping if the criteria wasnt found, but if it didnt find any, it would return true...hence NOT(true).

I checked my CF formula again, against your data on Sheet1, it does work. you had neglected to adjust the references to your actual data...see the attached

I hope this helps. The "List" is in column N.

Open up the Name Manager (Ctrl + F3)

You will see a Name called List >> Select this name and in the box at the bottom

Refers to: >> enter >> =Sheet2!$N$1:INDEX(Sheet2!$N:$N,MATCH(REPT("z",255),Sheet2!$N:$N))

Now the list will expand and contract as you add/delete names in column N

Once the conditional formatting is set you DO NOT have to update it everytime you add/delete something form column N.

You are very welcome and glad it worked out for you

Please don't forget to mark the thread as solved if the solution(s) answer your query.