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

 

[6StringJazzer]

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]

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]

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]

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.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[rlv01]

What color should a cell be if it contains more than one of the ingredients you are searching for?

 

[ashley1984]

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.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Thanks!

 

[Mentor82]

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]

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.

=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]

Herę I’m sending the formulae for 10 rows again because I made a mistake in the last post.

=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))