Conditionally Format for cells that do not contain....

[EBoudreau]

I'm looking to Conditionally Format a cell using a formula for another cell if it does not contain certain letters.

Example:

I need to conditionally format Cell B1 to be a certain color if Cell A1 does not contain "T","TC","M","MC","B", or "BC".
So when I type in anything other than those values, I format B1.

I also currently have another condition running in B1 in reference to A1 so this would have to format if the previous condition is not met.


Any Ideas?

 

[jorismoerings]

Hi,

1. Create a range with the value you mentioned. I used G1:G6 in my example.
2. Create the following conditional formula for B1: =ISERROR(INDEX(G1:G6;MAX(IF(ISERROR(FIND(G1:G6;A1));-1,1)*(ROW(G1:G6)-ROW(G1)+1))))
3. Place it as second formula to check after your first and check the box for stopping other conditonal rules if one is true. It's placed at the end of the rule set.

It looks like an array formula and when placed on a worksheet it is and needs to be confirmed with Ctrl-Shft-Enter but within Conditonal formula there's no need. The array formula can simply be entered.

HtH

 

[EBoudreau]

Thank you. I will give it a try see what I come up with!