Turn off Conditional Formatting

**martindwilson** · 12-13-2009, 07:22 PM

whats the existing conditional format?

**MattVarnell** · 12-13-2009, 10:52 PM

The formula I currently have in Conditional Format is =IF(Q41<A9,A9,""). In
Q41 I have a set date. In A9 is the formula =TODAY(). As you can see, when Today is less than the set date the cell is formatted to turn red.

In the cell that I have this Conditional Format, I will enter a value when it is necessary. Once a value is entered, I would like the cell color to go back to default. I know the simple solution would be to remove the conditional format once the value is entered, but since I am dealing with several hundred cells, I would like this to be automatic.

Any help is greatly appreciated.

**ConneXionLost** · 12-14-2009, 12:09 AM

Hi MattVarnell,

In Excel 2003, conditional formatting gives you three levels. It will apply itself to the first true condition it finds, then ignore the remaining levels.

Assuming the cell you have the conditional formatting applied to is X99; move the current conditional formatting to "Condition 2". Then in "Condition 1" set the formula to =Z99<>"" and set the format to your default setting.

Cheers,

**DonkeyOte** · 12-14-2009, 03:13 AM

I would suggest you merge your existing rule with CXL advised approach, ie use a single cell to denote as to whether or not CF is "active" - say Z99 holds 1/0 where 1 is "on" and 0 is "off" your CF becomes:

=Z99*(Q41<A9)

ie no real need to create additional rules... in XL terms only 0 equates to FALSE and in CF terms only TRUE output results in CF being applied
- the above will therefore multiple your on/off switch (1/0) with a Boolean (TRUE/FALSE) the outputs of which will be either 1 (TRUE) or 0 (FALSE)

=1*(TRUE) --> 1
=1*(FALSE) --> 0
=0*(TRUE) --> 0
=0*(FALSE) --> 0

Thus only the first combination will result in CF being applied - ie both "on" and date < today()