Conditional Formatting, highlight any cell that contains any of 21 words, e.g. egg OR carrot

[mick92627]

I am using Excel 2003. I have a spreadsheet that contains text. LOTS of text.

I want to search the cells of only one particular column, (column F) and any time Excel finds any one of 21 words that I have identified as important to me, Excel will change the format of that cell, say to a background color of red and font color of white bold.

So for example, if one of the 21 words I am looking for is the string "cat" and the cell contains "he can catch the ball" that cell would be formatted, because the string "cat" is contained in amongst the text in that cell.

I want it to search for any of 21 words that I have identified. I expect the answer is going to be under Format -> Conditional Formatting, and I am pretty sure has to use the logical function OR somehow (can you do 21 ORs?) because of the "3 condition" limitation in Conditional Formatting. But so far I have not been able to figure it out after much searching and experimenting.

Alternatively, if it has to invoke a VBA macro, I'll do that instead.

I tried one word at a time using Conditional Formatting but it erases the previous conditional format. So if I conditionally format for "cat", it highlights all the instances of Cat just fine. But when I subsequently conditionally format for "dog", it unformats all the instances where cat was highlighted and then just highlights Dog. VERY frustrating. Ultimately, I want to end up with a speadsheet where any cell in column F that contains any one of my 21 words is highlighted.

Thanks in advance for your help.

 

[Jonmo1]

Welcome to the board...

Put your words in a Range of cells, for example say Z1:Z21

Then Highlight your column (say column A)
Format - Conditional Formatting
Select "Formula Is"
Enter the formula
=MATCH($A1,$Z$1:$Z$21,0) <---Where A1 is the currently active cell (found in the Name Box to the left of the formula bar).
Click Format - choose your background color
Click OK
Click OK

If you want your list of words to be on a seperate sheet...
On that sheet, put your words in a range, say A1:A21
Highlight A1:A21
Click Insert - Name - Define
Type a name, like MyWords
Make sure the "Refers to" Box reflects the range holding your words
Click ADD
Click OK

Now, in the conditional formatting, change the formula to
=MATCH(A1,MyWords,0)


Hope that helps.

 

[Norie]

Eh, OR is not a variable, it's a worksheet function, and there is no 3 'condition' limit with conditional formatting.

If you do want to use OR, and I'm pretty sure you don't, there is a limit.

I can't recall off the top of my head what it is though, but you should probably be looking at other functions. eg MATCH, VLOOKUP etc.

 

[mick92627]

Welcome to the board...

Put your words in a Range of cells, for example say Z1:Z21

Thank You Jonmo1. I appreciate it. I did as you said. I put the 21 words into cells AP2:AP22

The I entered the Conditional Formatting formula:
=MATCH($F1,$AP$2:$AP$22,0) - where F1 was the top cell in the column who's cells I wanted to highlight.

I find that it highlighted some, though not all, of the cells which contained some words, and highlighted none of many other words.

For example, one of the words was ECM. If a cell contained "ECM", and ONLY "ECM", with no other characters, it would be highlighted. Whereas if a cell contained the string "System for eCMS", for example, it would not be highlighted.

So maybe instead if MATCH, it should be CONTAINS or some variation? Or maybe MATCH but with wildcards before and/or after ECM?

 

[mick92627]

Eh, OR is not a variable, it's a worksheet function, and there is no 3 'condition' limit with conditional formatting.

On your first point, yes, I caught that and changed variable to logical function, in my question. Thanks for the heads up.

On your second point, there is a 3 condition limit to conditional formatting, at least with Excel 2003. You create a condition, then you can add another, which it calls Condition 2, and you can add one more, but that's the limit.

If you're using a later version of Excel that no longer has this limitation, my apologies for the confusion.

 

[Jonmo1]

Thank You Jonmo1. I appreciate it. I did as you said. I put the 21 words into cells AP2:AP22

The I entered the Conditional Formatting formula:
=MATCH($F1,$AP$2:$AP$22,0) - where F1 was the top cell in the column who's cells I wanted to highlight.

I find that it highlighted some, though not all, of the cells which contained some words, and highlighted none of many other words.

For example, one of the words was ECM. If a cell contained "ECM", and ONLY "ECM", with no other characters, it would be highlighted. Whereas if a cell contained the string "System for eCMS", for example, it would not be highlighted.

So maybe instead if MATCH, it should be CONTAINS or some variation? Or maybe MATCH but with wildcards before and/or after ECM?

Try

=LOOKUP(2^15,SEARCH($AP$2:$AP$22,$F1))

Note, if one of your words is say "Cat"

It won't know the difference between "Cat" and "Catch" and "Vacation"

 

[mick92627]

Try

=LOOKUP(2^15,SEARCH($AP$2:$AP$22,$F1))

Note, if one of your words is say "Cat"

It won't know the difference between "Cat" and "Catch" and "Vacation"

Fantastic. Perfect. Thank you so much.

 

[Gerald Higgins]

In terms of how many conditions you can have, I think both you and Norie are right. Kind of.
First, when you say you can have 1 condition, then add another, up to 3, then yes, you are right. But bear in mind that you still have the default condition, which effectively gives you 4.
But in another way, you are not restricted to 3 conditions, you are restricted to 3 (or 4) FORMATS, and I think this may have been what Norie was referring to.

For example, you could have a format that applied if either of two conditions is true.
For example

=or(a1=1,b1=2)

From this, you will see that you can have many more than 3 conditions.
I think the restriction on ORs is the same as the restrictions on IFs, i.e. 7 in a single formula.
So, if you really have only 21 words to select, you could in theory set up 3 separate conditional formats, each one dealing with 7 of the words, and each setting an identical format.
Maybe not the best way, but this would be possible I think.
For example

=or(a1="cat",a1="dog",a1="cow"....

I haven't tried this myself (yet....)