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

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
77
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?
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,890
Messages
5,574,854
Members
412,623
Latest member
princexxa
Top