Conditional formatting - Or statement for 10+ different text words

[jlugo]

Hi

I've created a food journal and I want certain words to highlight in red that are junk food.
So far, I've been creating them individually, but would rather do one long condition to include all the text words that I want.

So if the cell value is = milk, soda, fries, pizza, beer, chips, candy, alcohol, mcdonalds, wendys, burger king etc. then it highlights in red.

Is is possible to add all these text into a multiple criteria OR statement?

 

[FatLane]

I've done projects that used multiple criteria for conditional formatting. What worked best for me was to create a junk food table somewhere (maybe a junkfood worksheet). Then you can reference that table in your conditional formatting. If the food item in question appears in the table, then the cell appears red.

since I wasn't able to reference other worksheets within the conditional formatting, I would create a helper/trigger collumn somewhere. If your food item was in cell B10: in cell AA10 I would enter =If(countif(junkfood!$A$1:$A$100,B10)>0,1,0)

Conditional format: =aa10=1

The advantage I've found is that I can add, delete or edit the items in the table without having to mess around with the conditional formatting.

 

[FDibbins]

You could shorten that to...
=countif(junkfood!$A$1:$A$100,B10)>0

CF works only on TRUE/FALSE so if found, it will give >0 which is TRUE

Or you could use =MATCH(B10,junkfood!$A$1:$A$100,0)

 

[jlugo]

Great suggestions and I agree with your solutions. I've seen this done before and didn't think of it. Makes sense. Thank you.

 

[FDibbins]

Happy to help