Change the colour of single word in text string based on another cell value

**joshnathan** · 06-03-2014, 06:08 AM

Hi All,

I'm really struggling to find a solution to this so some pointers would be great.

I have a words in cell C3 and C4 and some large strings that appear in cells F8 through F220. I'm trying to produce a piece of code that basically looks for the words in C3 and C4 and highlights them in F8 through F220 in red.

Whats the easiest way to go about doing this?

**Richard Buttrey** · 06-03-2014, 06:14 AM

Hi,

Do both words in C3 & C4 have to be present?
What are you trying to highlight in F8:F220? Any cell that contains one (or both) words or just the word in the bigger string.

If the former then you don't need a macro. You can use an =MATCH() function in an adjacent column (say G) to identiify where F contains the words and use conditional formatting to paint the relevabt cells some appropriate colour.

**joshnathan** · 06-03-2014, 06:38 AM

Hi Thanks for responding.

No both words don't have to be present just either or and ideally if i have a string in F8 for example that looks like this.
"The cat in the hat" and the reference in C3 is hat. I'd like the cell in f8 to look like "The cat in the hat" and any cell in the range F8:F220 that word should have that specific word highlighted.

**Fotis1991** · 06-03-2014, 06:45 AM

Hi

How about this?

Please Login or Register  to view this content.

**joshnathan** · 06-03-2014, 06:53 AM

Hi thanks for the response, i can't seem to get it to function though.
I changed the Row count to 8 to match my data set as it starts in F8. However, when i run it it doesn't change the colour of the word in question. For example in a test data set. I set C3 to cats and F8:F16 to All the cats and none of the cats were highlighted. I can't quite workout why this wouldn't work.

**Fotis1991** · 06-03-2014, 06:57 AM

Try in this way..

Please Login or Register  to view this content.

**romperstomper** · 06-03-2014, 07:00 AM

Fotis' code should have worked as it was posted. I'd prefer to store a couple of the calculations rather than redoing them each time:

Please Login or Register  to view this content.

but it shouldn't matter particularly.

**joshnathan** · 06-03-2014, 07:33 AM

Hi still cant get it to run and i'm not sure why. Everything is as it should be. I dont know if its because the string is pulled in from a vlookup which might cause the problem. But then again i can't get it to run on a test sheet where i've typed the values into C3 and F8:F16

**Fotis1991** · 06-03-2014, 07:38 AM

Sample using a formula to get value in C3 is attached.

**joshnathan** · 06-03-2014, 08:11 AM

hi i found the issue with my sheet my data in F8:F16 is a vlookup of data on another sheet. I can get Fotis's sheet to work when the data in F8:F16 is just copied as values but the moment i swap it out for data that references the adjacent cell containing the long text string it stops working.

**romperstomper** · 06-03-2014, 09:41 AM

It's not possible to format part of the result of a formula as a different colour. You would have to replace the formulas with actual values to do it.