Conditional formatting using named range as criteria

[dsbh]

Hello! I need to use a named range of words ("wordlist"):

red
green
blue
etc...

And I need to search for ANY cell in another sheet that may contain any of these words (at the beginning, middle, or end of a string), and highlight that cell in red.

I'm using Excel 2010, and I can't get my =COUNTIF(wordlist,A2) formula (with A2 being the start of the target data) to work with wildcards. It will only find cells that START with any of the strings int he named range used for searching.

Any help is appreciated!

 

[mikerickson]

Untested but try this CF on A1

=SUMPRODUCT(COUNTIF(A1,"*"&wordlist&"*")) < 1

 

[dsbh]

Ha! Thank you very much for the quick reply.

This worked, but it formatted the entire sheet red, leaving the cells containing words un-highlighted. How do I flip this result so only the cells with a hit are formatted?

 

[lenze]

Just a guess, but try reversing the <

SUMPRODUCT(COUNTIF(A1,"*"&wordlist&"*")) > 0

lenze

 

[dsbh]

Perfect! Thanks to mikerickson and lenze! At least I learned a lot about COUNTIFs today...

 

[mikerickson]

Oops. That's what I get for not testing.