• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

Conditional Formatting: Excel 2007

I have a column called DEADLINE (Column B).  I'm trying to use conditional formatting to color cells based on the deadline.  

I want the cell to turn red if the dealdine is equal to today's date or it has already passed, turn yellow if the deadline is within 10 days of today's date, or green if today's date is more than 10 days away.
Here's what I tried but it's not working:
=B3>=(TODAY()+11)   formatted to turn Green
=B3<=(TODAY()+10)   formatted to turn Yellow
=B3<=(TODAY())         formatted to turn Red

Any help would be appreciated.
1 Solution
dplearningAuthor Commented:
Thanks Tripyre but this won't work becuse this only works for values in the cell.  I actually need it to calculate a formula.
barry houdiniCommented:
in conditional formatting the conditions are apied in order, so with the coditions you have all past dates will be yellow

Try switching conditions 2 and 3 (you can move them up and down with the arrows)

Regards, barry
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Try changing the Yellow condition such that it is:
Cell Value between =TODAY()+1 and =TODAY()+10
(See attached screen shot)

Rory ArchibaldCommented:
You can also just set the Stop if True option for condition 2.
barry houdiniCommented:
Hello Rory,
I'm not sure how that would work here. If the conditions are in the order given by the asker then past dates will still be formatted yellow.
In any case, if the formatting of the conditions is mutually exclusive, e.g. assuming green fill, yellow fill, red fill then "stop if true" effectively applies anyway even if not explicitly set.
What always annoys me about conditional formatting in Excel 2007 is that the first condition you set up actually defaults to the last condition on the list, so if the conditions are applied in the order shown they'll actually be placed in reverse order an that should work as required......
regards, barry
Rory ArchibaldCommented:
You're quite right - brain fart on my part. Definitely pub time for me... :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now