Excel VBA to Search if Cell Contains Specific Text and highlight

ashley1984

New Member
Joined
Mar 31, 2018
Messages
21
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
842
Office Version
  1. 365
Platform
  1. Windows
I think you are making this more complicated than necessary. You could do this using conditional formatting with no VBA code. If you are interested I can post an example. You could even have a dropdown list of all possible ingredients, instead of typing it in from scratch.
 

ashley1984

New Member
Joined
Mar 31, 2018
Messages
21
Office Version
  1. 365
Platform
  1. Windows
I think you are making this more complicated than necessary. You could do this using conditional formatting with no VBA code. If you are interested I can post an example. You could even have a dropdown list of all possible ingredients, instead of typing it in from scratch.
Ok thanks. Could you post and example? The problem I have is I'm looking to search on a number of ingredients and while I have a list of ingredients currently I might want to search on a different ingredient that's not part of the conditional formatting rule. I could add to the rule but the workbook is not just used by me and I want to make it simple for the other users. Your way might be simpler once i see the example

Thanks
 

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 recommend so you let us know exactly what you need as a result, the conditions to be met and range in which you want to check the conditions. It would be good if you cwiuld upload an example of the data you have do we could have a look. I believe the conditional formatting can solve the issue by writing the proper formulae.
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
842
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here is a sample file. It is set up to allow two ingredients in column L. You can add more rules to add more rows of ingredients.

 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
What color should a cell be if it contains more than one of the ingredients you are searching for?
 

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,
Thanks for uploading the file.
Use the folloging conditional formating formulae starting from the N1 =FIND($L$1;$N1;1) and set the range =$N:$N and the color you wish to highlight if the L1 text is found :)
 

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
Here, you have the formulae for 10 rows L1 to L10 which you can use i conditional formatting.
Let me know if that’s what you need.
Code:
=OR(FIND($L$1;$N1;1);FIND($L$2;$N1;1);FIND($L$3;$N1;1);FIND($L$4;$N1;1);FIND($L$5;$N1;1);FIND($L$6;$N1;1);FIND($L$7;$N1;1);FIND($L$8;$N1;1);FIND($L$9;$N1;1);FIND($L$1;$N10;1))
 

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
Herę I’m sending the formulae for 10 rows again because I made a mistake in the last post.
Code:
=OR(FIND($L$1;$N1;1);FIND($L$2;$N1;1);FIND($L$3;$N1;1);FIND($L$4;$N1;1);FIND($L$5;$N1;1);FIND($L$6;$N1;1);FIND($L$7;$N1;1);FIND($L$8;$N1;1);FIND($L$9;$N1;1);FIND($L$10;$N1;1))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,288
Messages
5,635,334
Members
416,856
Latest member
silentir

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