Excel VBA to Search if Cell Contains Specific Text and highlight

ashley1984

New Member
Joined
Mar 31, 2018
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 30, 2018
Messages
311
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
I’m glad I could help. 🙂
Take care.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
311
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Hi,
I’m glad I could help.
Take care.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,883
Messages
5,627,458
Members
416,249
Latest member
yogaraj IND

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top