Forum Discussion
Identifying Multiple Text Matches in Excel
Hi, I have the below fields in one sheet: Sheet 1 INGREDENTS* BHA, Coal, Amber In another sheet (sheet 2) I have the below ingredients listed. What I would like to do is identi...
It would be much easier if you entered the three ingredients on Sheet 1 in three separate cells. You can then use conditional formatting - see the attached sample workbook.
With the three ingredients in a single cell, you need VBA:
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
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
End SubDemo workbook attached.
HansVogelaar Your amazing!! thank you so much Hans. I used the VBA and it worked thank you so much 🙂
- If I wanted the button to also highlight ingredient that matched the ingredient sheet in Sheet1 from cell A2:A100 how would the code in VBA change?
- 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
