Identifying Multiple Text Matches in Excel

peiyezhu
Bronze Contributor
Dec 07, 2022
Sub Button1_Click()
Dim r As Range
Dim c As Range
Dim s As String
Dim p() As String
Dim w As Variant
With Worksheets("Sheet2")
Set r = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With
for each c in sheets("sheet1").range("A2:A100")
rem Set c = Worksheets("Sheet1").Range("A2")
c.Font.ColorIndex = xlColorIndexAutomatic
s = c.Value
p = Split(s, ", ")
For Each w In p
If Not r.Find(What:=w, LookAt:=xlPart) Is Nothing Then
c.Characters(Start:=InStr(s, w), Length:=Len(w)).Font.Color = vbRed
End If
Next w
next
End Sub
- Tenielle
  Copper Contributor
  Dec 18, 2022
  peiyezhu thanks so much for looking at this for me, but I can't seem to get it to work are you able to have a look at in my spreadsheet see attached and let me know what I am doing wrong? Really appreciate your help 🙂
  Ingredience Button.xlsm40 KB
  - HansVogelaar
    MVP
    Dec 18, 2022
    Tenielle
    
    Your code has
    
    With Worksheets("Ingredience_List")
    
    but the name of the sheet is Ingredience List with a space instead of an underscore.
    
    So it should be
    
    With Worksheets("Ingredience List")
    
    Similarly, you should use "Online Product Sheets" instead of "Online_Product_Sheets".
    
    And the comma-separated lists of ingredients are in column K of Online Product Sheets, not in column J, as far as I can tell. However there isn't a single match with column A of the Ingredience List sheet, at least not in your sample workbook.
    
    Try this version:
    
    Sub Button1_Click() Dim r As Range Dim c As Range Dim s As String Dim p() As String Dim w As Variant With Worksheets("Ingredience List") Set r = .Range(.Range("A4"), .Range("A" & .Rows.Count).End(xlUp)) End With For Each c In Sheets("Online Product Sheets").Range("K7:K100") c.Font.ColorIndex = xlColorIndexAutomatic s = c.Value p = Split(s, ", ") For Each w In p If Not r.Find(What:=w, LookAt:=xlPart) Is Nothing Then c.Characters(Start:=InStr(s, w), Length:=Len(w)).Font.Color = vbRed End If Next w Next c End Sub

Forum Discussion

Identifying Multiple Text Matches in Excel