Hi all,

As of now, I have a data validation list per cell that allows the selection of multiple values, separated by a comma.

I have written a macro that converts the selected value to a shorthand abbreviation code.

I have conditional formatting apply a bg color change to the cell if a single value is valid in a list.

I am trying to have the bg color change occur if multiple values are selected in a single cell as well.

For example...

Say I have a column titled "Color" and I have a list of options to choose from: Blue, Red, Green, Yellow,

I have a mapping in a separate column for each color as follows: Blue - BL, Red - RD, Green - GR, Yellow - YW.

Now, I want to validate that all the values mapped correctly according to the mapping above with conditional formatting. i.e. the bg color should change for the value abbreviations that match the mappings list provided.

So, as of now I have column values BL, RD, RD, GR and they all have a bg color change because they are all present in my mapping list. But, if a single cell was converted from "Blue, Red" -> "BL, RD", there is no bg color change because I guess Excel is reading it as "BL, RD" as a whole, which is not a value in the mapping.

How do I have it change the bg color if all values in the single cell match those in the mapping list provided?


I hope that all made sense...

Please ask for any clarification if needed!

Thank you!