Excel Conditional Formatting of list of text

[JanBS]

I have an Excel file that uses an MS Query. This will get refreshed on a regular basis. On one column I would like to apply conditional formatting.

The column is called Bin ID. If the value of the Bin ID contains "RED", "WHITE", "GREEN", "BLUE", "YELL","BLACK", "BOX" or "BENCH" (such as "RED01, BOX78, WHITE24, etc.) then I need the cell to have a Green fill. If it is not blank/null and it has any other Bin ID that does not contain those then it needs to have a Red fill. Blanks/nulls should have no fill. This needs to be applied to the entire column because the number of rows always changes.

Can someone tell me what the formula would be for the conditional formula? I am using Excel 2010.

Thank you very much for any help you can provide.

Jan Schmitt

 

[SkipVought]

Hi,

First make a list of the values you have outlined in your requirement. Give that list a Range Name of MyList, for instance.

Using the Conditional Format feature, you'll need a formula for each format like, assuming that this column is Z...

=MATCH(Z2,MyList,0)>0

 

[SkipVought]

BTW, you'll need to select the entire [Bin ID] in the ListObject table before you do the CF.