Search a string of text and highlight specific words within the string that match any cell in a reference column

[mp1021257]

Hi,

I am trying to write a macro that will search three strings of text in Range (E6:E8), by comparing them to cells in a different worksheet. The cells in the other worksheet are in Range (C4:C100).

For example, lets say in cell E6 there is a list of names: Tom, Matt, Bob, Jim, Kathy, etc.

In cells C4:C100, I have another list of names:

Jack
John
Jimmy
Tom
Joel
Bob


After running the macro, I would like the names Tom and Bob (the names from E6 that match up with cells in C4:C100), to be bolded and red. Specifically: Tom, Matt, Bob, Jim, Kathy, etc.


Thanks for the help.

Best,
Mark

 

[JoeMo]

You don't provide much detail and your example is limited. The code below is designed to address you singular example. That is, the string in any cell is characterized by sub-strings separated by comma and space delimiters. I'll assume the strings are on the activesheet when the code is run, and the list is on Sheet2 - change to suit. Also assume there are no duplicate sub-strings in a cell.

Sub HiLiteMatches()
Dim Sht As Worksheet, S As Variant, R As Range, c As Range, i As Long
Set Sht = Sheets("Sheet2")
Set R = Sht.Range("C4:C100")
Application.ScreenUpdating = False
For Each c In ActiveSheet.Range("E6:E8")
       S = Split(c.Value, ", ")
       For i = LBound(S) To UBound(S)
              If Not IsError(Application.Match(S(i), R, 0)) Then
                     With c.Characters(InStr(c.Value, S(i)), Len(S(i))).Font
                            .Bold = True
                            .Color = vbRed
                     End With
              End If
       Next i
Next c
Application.ScreenUpdating = True
End Sub

 

[mp1021257]

Joe,

This is exactly what I was looking for. All your assumptions were accurate.

Thank you!

 

[JoeMo]

Joe,

This is exactly what I was looking for. All your assumptions were accurate.

Thank you!

You are welcome - thanks for the reply.