Conditional formatting for due date tracking

[ashish514]

Hi,

I have a tracking sheet with various columns having dates for each milestone of a project which I need to track.
I am using conditional formatting which highlights all past dates in red colour and the dates occuring within next 3 days in orange.

I have made an adjacent column which says whethere the task is "completed" or "pending".

What I need now is to find some way to highlight the dates in green wherever the adjacent column shows "completed" and keep it red whrever it is "pending".

How can this be done? Also, is there any other way by which I can avoid one more column (completed/pending column), though I can't see how it is possible.

 

[etaf]

you can use and AND() statement in the rule

use a $ and the cell that contains the words

for exampleif the cell with pending/completed is in cell M2

AND( DateFormulaYouHave , $M2="pending") - RED
AND( DateFormulaYouHave , $M2="completed") - Green

or if all the cells are to be green or red
just add two new rules

=$M2 = "pending" - format Red
and put that rule at the top of all the rules and use the stop if true - if using 2007,2010,2013 versions of excel