Highlight all the cells if the keyword in the cell matches to any value of a list

Question

For one of my excel sheet, it is required to highlight the cells that has specific keyword in it. To solve this, i have created a list called my_list which contains value1, value2 & value3.

I have used below formula for highlighting the column D

=OR(ISNUMBER(SEARCH(my_list,D1)))

It's working fine for the column D

I want this to be extended for all the columns in the excel sheet

its very tedious to write the above formula for all the columns

is there any change that can be made to above formula which works for all the columns/entire excel sheet?

I tried with below which didn't help

=OR(ISNUMBER(SEARCH(my_list,my_excel)))

where my_excel is the selection to all cells in the excel sheet

can you please help with me this?

Answer 1

oops - I focused too much on the formatting issue and not on the formula. You need to change it. So here is a revised (and correct) answer:

You can select the entire worksheet by clicking the upper corner and then apply conditional formatting to all of it as follows:

select New Rule. Then select Use a formula...

end enter this formula using MATCH because SEARCH will convert the numerical contents to text and then look at the characters in mylist. If you have numerical values in mylist or in your cell contents, SEARCH will give you false positives. So change it to this:

=OR(ISNUMBER(MATCH(mylist,A1,0)))

Make sure that there are no $ in the address for A1.

Set your formatting and this should work when you click OK or Apply.