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 identify whether any ingredient names that appear in the above cell (multiple ingredient names with spaces or comms between them, regardless of sentence case) also appears in the below list.
I would like to highlight the matches in the cell above (Sheet 1), for example BHA and Coal would both be in red as they appear in the list below but Amber would stay black, can anyone help? I have been trying to figure this out for hours
Sheet 2
| INGREDIENTS* |
| Aluminum Chlorohydrate |
| 1,4 - Dioxane |
| Acrylates |
| Animal Derived Ingredients |
| Avobenzone |
| BHA (Butylates Hydroxyanisole) |
| BHT (Butylates Hydroxytoluene) |
| Butoxythanol |
| Cadmium |
| Chemical UV Blocks - Titanium, Benzophenone, Oxybenzone, Homosalate and Octinoxate. |
| Coal Tar |
| Cyclic Silicones (also known as Siloxanes) |
| Ethanolamines |
| Ethylenediaminetetraacetic (EDTA) |
| Foraldehyde |
| Hydroquinone |
| Lead |
| luminum Powder |
| Mercury and Mercury compounds |
| Methyl cellosolve |
| Methylchloroisothiazolinone |
| Methylisothiazonlinone |
| Mineral Oil |
| Nanoparticles |
| Nickel |
7 Replies
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?
