Conditional Formatting: If at least one cell in a row contains at least 1 of 100+ options

[Nglaze]

Is there a way to setup a conditional formatting formula to highlight if at least one cell on a row contains at least 1 of 145 possible codes.

Column O contains a list of 45 codes. I need to know how to check that each row contains at least one of the 145 codes listed. There are over 1,000 Rows and about 6 columns of data.

I have done this with a Search Box but it only works if 1 code is listed in the box (e.g. LL41.4). I want to avoid creating 145 search boxes.


-- removed inline image ---




Thanks for your help.

 

[Special-K99]

Try


Select A1:N1 where A1:N1 is the row you want to check

Conditional Formatting
New Rule
Use a formula to determine...

=(SUMPRODUCT(--(A1:N1=O$1:O$145))>0)

Format as required

Use Format painter (paintbrush icon) to copy to other cells