Excel VBA to Search if Cell Contains Specific Text and highlight

[ashley1984]

Hi,

I have a large range of cells with ingredients, example below:

Water, Pea Protein*(16%), Rapeseed Oil, Coconut Oil, Rice Protein, Flavouring, Stabiliser (Methyl Cellulose), Potato Starch, Apple Extract, Colour (Beetroot Red), Maltodextrin, Pomegranate Extract, Salt, Potassium Chloride, Concentrated Lemon Juice, Maize Vinegar, Carrot Powder, Emulsifier (Sunflower Lecithin), *Peas are legumes. People with severe allergies to legumes like Peanuts should be cautious when introducing pea protein into their diet because of the possibility of a pea allergy

I've written some VBA code where I type an ingredient (e.g. Pea Protein) into a cell (in my workbook it's cell L1) and then used a command button to search my range (N3:N1267) and highlight any cell that contains the ingredient I am searching for e.g. Pea Protein.

I'm now trying to keep all cells with pea protein highlighted (green) but then run another search on an different ingredient and highlight the cells a different colour e.g. blue. I'll need to repeat this for several protein types

I think I need to use a if_loop () but not sure where to go from there. The code i have so far is below:

Private Sub CommandButton1_Click()


Dim text As String
Dim myrange As Range

text = Worksheets("Fresh").Cells(1, 12).Value

Set myrange = Worksheets("Fresh").Range("N3:N1267")

myrange.Interior.Pattern = xlNone

For Each cell In myrange

If InStr(LCase(cell.Value), LCase(text)) <> 0 Then
cell.Interior.ColorIndex = 4
End If

Next
End Sub

any help would be appreciated

 

[Mentor82]

I’m glad I could help. ?
Take care.

 

[Mentor82]

Hi,
I’m glad I could help.
Take care.