Excel VBA to Search if Cell Contains Specific Text and highlight

ashley1984

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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
What color should a cell be if it contains more than one of the ingredients you are searching for?
 
Upvote 0
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 :)
 
Upvote 0
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))
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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
Back
Top