Highlight cells whose strings contain the same word

[kgartin]

This sounds like it might be a pretty easy one but my brain is fried right now...

Range is A1 - A7

Cell A1 = "Food Lion"
Cell A2 = "Kroger Food Store"
Cell A3 = "Kaybee Toys"
Cell A4 = "Burger King"
Cell A5 = "Winn Dixie"
Cell A6 = "Bev's Burger House"
Cell A7 = "Toys R Us"

Each of the cells that contain the same word in their string needs to be highlighted a unique color. So A1 and A2 = Yellow. A3 and A7 = Green. A4 and A6 = Blue.

How?

 

[kweaver]

Lion King would make this fairly complicated, no?

 

[kgartin]

Lion King would make this fairly complicated, no?

I was wrong...I only THOUGHT my brain was fried. Thank you.

 

[CakeisaDie]

Can't you just do conditional formatting?

Home > Conditional Formatting > Highlight Cells Rules > Text that Contains
Other option is to use a isnumber to search with conditional formatting.

=ISNUMBER(SEARCH("What you are searching for" ,"within what cell")

 

[kgartin]

Can't you just do conditional formatting?

Home > Conditional Formatting > Highlight Cells Rules > Text that Contains
Other option is to use a isnumber to search with conditional formatting.

=ISNUMBER(SEARCH("What you are searching for" ,"within what cell")

No, it's variable. I just need cells highlighted that have the same word(s) in their string.

It's kind of an "Inception" situation that I have and this is a Band-Aid. Basically I have a range of cells in a worksheet that contain names (manually typed in with no real pattern) and another range in another sheet with similar names that are supposed to match. So one sheet may have FOOD LION and KROGER FOOD STORE and the other sheet has FOOD LION and KROGER. Well I have a code that isolates each word of every cell in the range in one of the sheets (using split) and compares each isolated word to every cell in the range in the other sheet (using instr()). If it finds a match, it does something (collects associated numerical data). It works great until it finds a match I don't want. In those cases, I want to highlight the cells so that a third party knows to question the collected data.