Highlight words/phrases from a list

Hi all,

I am a complete novice with macros and VBA

I have a list of words in column G, and sometimes two words or phrases with a comma. I am trying to highlight on each line in column E, any data which is relating to the cell in column G

For example, Cell G2 has 0
I want 0 to be highlighted in cell E2 in red


If cell G3 says: service,delivery I want cell E3 to have the words service and delivery highlighted in red.


I am trying this:
Sub HL()
Dim r As Range, l As Long
For Each r In Range("E2", Range("E99999").End(xlUp))
l = InStr(1, r.Value, r.Offset(0, -6).Value, 1)
If l Then r.Characters(Start:=l, Length:=Len(r.Offset(0, -6).Value)).Font.Color = vbRed
Next
End Sub

Can somerone please help? My spreadsheet is attached

Please try

Please Login or Register  to view this content.

Hi Bo_Ry

thank you do much!

Is there a way to make this work for multiple words i.e. cell E1574 and a few after that have two words: paid,00

for example?

Thank you!

Try

Please Login or Register  to view this content.

Thank you Jindon, unfortunately this is returning a type mismatch error and then also it is highlighting most of the first set of words but then it doesn't highlight the rest and definitely does not highlight multiple words.

Any suggestions please?

I Forgot to change the red t.

Please Login or Register  to view this content.

Thank you both, looks like some errors were being caused due to NAME errr. You have both been very helpfuil!

Originally Posted by Bo_Ry

I Forgot to change the red t.

Please Login or Register  to view this content.

Hello,

All. I want to re-open this thread.

How can I be helped with my issue that case sensitive and/or partial matches are not being picked up? So in the workbook in this thread, row 1347, the words 'not used' from column G does not highlight the words 'Not used' in column E because of the capital N in the word 'Not. What if my text said not used. (lower case and full stop?) can someone advicse and help me update the macro?

thank you

Add Option Compare Text

Please Login or Register  to view this content.