Conditional formatting to bulk mark a list of words or similar function?

[Casey825]

Hi there and thanks in advance for any help given. You guys have been tremendously helpful.

I have been using the conditional formatting function to find and mark cells in a column which contain various words. The list of words is getting quite long now and I was wondering if there is a quick way to cross check the list and do the conditional formatting as a bulk formula or VBA command? Would prefer formula is possible. Or can I even use the conditional formatting function and use a separator and do all the words at once? Thanks so much.

Casey.

[Special-K]

I saw something like this a few minutes ago possibly on this forum (maybe on another forum I frequent!)
They were talking about using a named range to define a list of words so the formula can just stay the same.

[Casey825]

I'm trying to use the following formula in the conditional formatting - new rule function:

=SUM(COUNTIF(A2,"*"&Mylist&"*"))

But it's not highlighting all the correct cells and highlighting some that shouldn't be. Any ideas?

[Casey825]

Formula like this works:

=OR(ISNUMBER(SEARCH("red",D1)),ISNUMBER(SEARCH("orange",D1)),ISNUMBER(SEARCH("purple",D1)))

Just replace D1 with the column you are formatting and replace red, orange etc with your texts.