Problem with Highlighting certain phrases

[Excell1677]

Hello,

I've ran into a situation where I'm trying to highlight all instances of "pizza deal" the problem is that with the symbols in the word it doesn't highlight all the words such as "pizza deals, online pizza deals, etc." My goal is as long as it has the words "pizza" and "deal" it should highlight that particular cell. I thought maybe using vba could do the trick, but I ran into a dead end again.

Another option I was thinking about (that I created on the second tab) is having a list of the words I'm looking for such "pizza deal", "salad deal" and it would highlight all those instances including "online salad deals", "deals for pizza" etc in the other list. Any help on this would be appreciated...

Thanks

[kersplash]

Try this in H2 and copy down;

Please Login or Register  to view this content.

[Excell1677]

Hi Kersplash,

Thanks for taking time to look at this. I tired the formula and was wondering is there a way to modify this so that it would just highlight the phrases in B2?

[kersplash]

Do you mean highlight the cells in Column B that contain the words pizza & deal?

[Excell1677]

Yes, the goal is to highlight all instances of "pizza deal" in column B whether it has symbols in front or not or where the word "pizza deal" is positioned. i.e. online pizza deal

[AlKey]

Go to Conditional Formatting and choose "Use a formula..", Enter formula and apply color.

Please Login or Register  to view this content.

[kersplash]

Put this rule in B2 then copy the formatting down Column B.

Capture.JPG

[Excell1677]

Hi AlKey,

I tired that method and it selected the majority, but didn't select all of them. I provided a screenshot to show what was captured...

[AlKey]

There is more than that if you scroll down
Please see attached file

[Excell1677]

Thanks Kersplash,

Another question I wanted to build upon this would be if I had multiple names I wanted to check like I wanted to find all instances of "pizza deals" and "taco deals" would the best method be the one you suggested and just change the name. Or create a list of all the words I'm looking to highlight and have it highlight any matches of the word. I provided an example of this...

[kersplash]

There's many ways to tackle the same problem.

I like Alkey's solution so just add rules for each set of words and colour them differently.

[Excell1677]

Thanks Alkey,

One more question what would your recommendation be if there were multiple names that needed to be checked. For instance "pizza deals", "salad sales", etc. Is it possible to add more that one text string in that field? For example could I use..

=Countifs(B2,"taco","salad",B2,"deal","sale")

[Excell1677]

Thanks for your help kersplash, I agree Alkey's method is more manageable since I'll be dealing with hundreds of these kind of keywords..