Conditional formatting with multiple rules and blank text cells [Solved]

PaiviM 8 Posts Friday December 1, 2017Registration date December 30, 2017 Last seen - Dec 1, 2017 at 09:25 AM - Latest reply: PaiviM 8 Posts Friday December 1, 2017Registration date December 30, 2017 Last seen - Dec 6, 2017 at 10:00 AM
Hi,

I wonder if someone would be able to help me with this. This is probably really simple, but I can't get it to work. I need excel to change a cell to red if the due date on that cell is before today's date (date on that cell will change based on the date that is input on another cell), but only if the cell next to it has been left blank.

So if cell G2 has had a comment written on it, then cell H2 should be clear, regardless of the due date. If G2 has been left blank then H2 should be red if today is past the date on the cell. Hope that makes sense.

I've used this formula to determine if cell is past due date =\$H\$2<TODAY() and it would format the cell red.

Paivi
See more

6 replies

ac3mark 8645 Posts Monday June 3, 2013Registration dateModeratorStatus January 22, 2018 Last seen -
0
5
So, are you asking about conditional formatting, or how to nest different logic statements?

With your example, it looks like you can do the conditional formatting.

You can have a list of lets say 10+ different logic tests to return a true with Conditional formatting. Just stack the logic from least to most significant. In other words, every single logic test can make a formatting change to the cell! It is the last "True" logic test that wins!

It's kind of fun to do the impossible! -Walter Elias Disney
PaiviM 8 Posts Friday December 1, 2017Registration date December 30, 2017 Last seen - Dec 4, 2017 at 06:41 AM
Hi!

Thanks for your reply! This is what I've got as conditional formatting rules at the moment, in this order.

=\$H£2=TODAY() applies to =\$H\$2 Formats as orange
=\$H\$2<TODAY90 applies to =\$H\$2 Formats as Red
=\$G\$2="" applies to =\$H\$2 formats as clear

So what I'm trying to achieve is that cell H2 goes to orange or red if the date on that cell is today or earlier, consecutively, but if a comment has been left on cell G2 then it will cancel the other rules and leave cell H2 clear. I'm not sure if I need to add another rule, and whether they are in right order, and if I need to click one of them as "stop if true".

Much appreciate your time on this!

Kind regards,

Paivi
ac3mark 8645 Posts Monday June 3, 2013Registration dateModeratorStatus January 22, 2018 Last seen - Dec 4, 2017 at 05:10 PM
Paivi,

Have you attempted to make any changes based on the input I have given? Are you still stuck?
PaiviM 8 Posts Friday December 1, 2017Registration date December 30, 2017 Last seen > ac3mark 8645 Posts Monday June 3, 2013Registration dateModeratorStatus January 22, 2018 Last seen - Dec 5, 2017 at 03:34 AM
Hi,

Yes I organised the rules as per my above list, but no success. The colours change based on the formatting rules, but I can get the format to go clear in cell H2 once something has been input in cell G2. I'm not sure what I'm doing wrong or possibly not doing.

Many thanks!

Paivi
ac3mark 8645 Posts Monday June 3, 2013Registration dateModeratorStatus January 22, 2018 Last seen - Dec 5, 2017 at 04:49 PM
Make the formatting go away if:

G2 <> ""

Give that a try. Place that one last.
PaiviM 8 Posts Friday December 1, 2017Registration date December 30, 2017 Last seen - Dec 6, 2017 at 10:00 AM
Hi,
it works!! Thank you so much! You can't imagine how happy that's made me :)

Paivi
Respond to ac3mark