LinkBack URL
About LinkBacks
I have a color change alert in a cell using Conditional Formatting. Is there a formula that will turn off the alert in that cell once a value, (any value), is entered into the cell?
Thanks!
I have a color change alert in a cell using Conditional Formatting. Is there a formula that will turn off the alert in that cell once a value, (any value), is entered into the cell?
Thanks!
whats the existing conditional format?
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
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.
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,
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
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()
Last edited by DonkeyOte; 12-14-2009 at 04:05 AM. Reason: inconsistent ref. in relation to CXL's prior post - modified to Z99
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
why not just
=Q41<A9
then as long as q41 has a date >=today or text it will be default
Good point Martin - serves me right for reading responses and not questions...
MattVarnell, ignore my post altogether - apologies for confusion caused.
Thanks for your help! However, I feel that I have not properly explained my problem. Please see the attached example sheet. Hopefully, that should shed some light on what I am looking for.
we are back to what connexionlost suggested
on your example
condition 1 is (formula is option)
=B9<>"" choose no fill
condition 2 is (formula is option)
=G7<B3 choose red
you dont use the =IF(G7<B3,B3,"") in cf as it ony tests true/ false
I'm not much of logician but could you maybe nest if statements to make the cell require two conditions (the value comparison and the empty status)before it turns red.
Or somehow otherwise If(AND)? use both the value comparison and the empty status to make it red? (Which is I think what the other guys are suggesting)
You sound like your way ahead of me on the excel front so would know why this aint possible sorry if I'm just being dumb.
Last edited by opsinmena09; 12-14-2009 at 11:30 AM.
fair point ,same difference but saves a condition
=AND(B9="",G7<B3)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks