Excel 2003: How to conditional format if cell contains any of a list of keywords

[doublefour]

Hi,

I've been able to find answers to dozens of questions in this forum that have helped me hugely, which I'm extremely grateful for, but I've come unstuck with this one....

I have a workbook split into two sheets - 'Data' and 'Keywords'.

The 'Data' sheet consists of a range of cells ($A$2:$AE$100), some of which contain numbers, some contain text strings, some a mixture of both (I don't know if that's relevant or not).

In the 'Keywords' sheet I have a list (from A2:A50) of keywords for positive sentiments, e.g. happy, brilliant, first class, at ease, outstanding etc. etc.
If it helps, I've defined this list as a name ("Keywords")

I'd like to apply a conditional format to cells $A$2:$AE$100 in 'Data', so that if any of those cells contain any of the keywords, the font colour in that cell changes to red.

e.g. If B2 contains the string "I AM HAPPY TODAY", and C3 contains the string "I AM SAD TODAY", all text in cell B2 would change to red font as it contains one of the keywords (happy), whereas the font colour of cell C3 would remain as is.

I'm open to using VBA if it'd be more appropriate than conditional formatting

Any help would be greatly appreciated.

Thank you

[dilipandey]

HI DoubleFour,

Welcome to the forum.

See the attached workbook where I have used below formula in a column next to keywords:-

Please Login or Register  to view this content.

After this, have used a simple match function in the conditional formatting logic to achieve the desired results... let me know if you need further explanation on any point. Thanks.

CF if cell contains any keyword.xlsx


Regards,
DILIPandey

<click on below 'star' if this helps>

[Ron Coderre]

Using your posted scenario....
• Select the range of cells that will have Conditional Formatting, with A2 as the active cell
• Format.Conditional_formatting
...Condition_1: Formula is: =COUNT(SEARCH(" "&Keywords&" "," "&A2&" "))
...Click: Format...set the conditional format you want
...Click: OK...Click: OK

That will apply the CF to cells that contain stand-alone keywords .
Example:
Highlight "I am happy"
but, not "I am unhappy"

Is that something you can work with?

[doublefour]

To DILIPandey and Ron,

thank you both for taking the time to look at this problem and offer your solutions, both work absolutely as I needed.

I've implemented Ron's solution purely as I found it the more "simple" of the two, and I am a very simple person!

Thank you once again - stars well & truly deserved to both

[dilipandey]

You are welcome doublefour...

Thanks for the REP

Regards,
DILIPandey

<click on below 'star' if this helps>