Conditional formatting rules aren't working

[sbair123]

I have a spreadsheet where I am trying to track documents that are missing or expiring.  I have a rule in place for cells to be yellow if blank, green if the expiration date is in the future and red if the date is today or in the past.  The yellow and green are working but the red is not.  Any cell that has information is green.  This is how my rules are written, why is this not working? 

C:J

Highlight cell with

Blanks

Yellow

E2:G1048576

Highlight cell with 

Cell value greater than or equals to

="Today's Date"

Red

E2:G1048576

Highlight cell with 

Cell value less than

="Today's Date"

Green

[#2]

Hi @sbair123 

conditional formatting rules can be tricky (and frustrating, sometimes).

If you have multiple rules that are applied to the same cell range, the order of the rules is important. The first match wins.

Example: Although cell A5 is empty, it is shown in green and not in yellow, because the green rule comes first. As the green one says, if cell content < today, this is a match (empty is < than todays date).

In this example, the rules work as expected, because the yellow rule matches before the green one will fire:

So maybe you just need to rearrange the sort order of your rules.

The other option is the check box "Stop if True". If different rules would apply different types of formatting (lets say one rule changes the font color, the other rule changes the fill color), both rules would be applied, because they change different formatting options.

If you want to avoid this, you can stop at the rule where you put the checkbox.

[#3]

@Martin_Weiss 

Thanks so much for your reply.  I have already tried your suggestions of changing positions of the rules as well as marking the "if true then stop" box.  Neither of these seem to rectify the situation.  I've even deleted my rules and re-added them (now the red works instead of the green) but it appears I'm still able to only get one or the other of these to work.  

Thank you for your suggestions, at least I know that I'm trying the right things.  If you think of anything else to try please let me know.