# Conditional Formatting if Cell Contains Certain Character



## AnetShe (Dec 14, 2022)

Any help would be amazing - I've built an order form in excel and using conditional formatting I want to have the size value columns that are in the 'size scale' entered not have any color format (as all size values cells start with being formatted with Grey). Basically just want to show people where they are allowed to enter order units in - example, if S,M,L is entered in the 'size scale' column then only those three size should have no format/be white).
I am using this formula for the conditional formatting - =ISNUMBER(SEARCH("S",$Z6)).
I've run into a problem because my size scale info has characters that the conditional formatting is picking up when it shouldn't - snapshot attached of this example.
If I have conditional formatting looking for an 'S' it will correctly remove formatting in the 'S' (size Small) column, but also will incorrectly remove it in the 'S' column if the value in the 'size scale' column is XS (extra small) - because it includes the 'S'.

Let me know if you need more info - I am open to any way to automate this (will have approx 2,000 rows in sheet)!
The want is for the size value column to have formatting removed if it is within the size scale column.
Am also open to the reverse, how can I grey out all size columns not in the size scale field.

THANK YOU!!


----------



## Rick Rothstein (Dec 14, 2022)

(untested) Try this... =ISNUMBER(SEARCH(",S,",","&$Z6&","))


----------



## AnetShe (Dec 14, 2022)

Rick Rothstein said:


> (untested) Try this... =ISNUMBER(SEARCH(",S,",","&$Z6&","))


THIS WORKS!! Just tested a number of size scale variations and it looks good - huge thank you!


----------

