Formatting based on percentage complete and target date

Hello,

I have searched all over and can't seem to find anything or figure this out myself. I have a sheet (example is attached) that lists multiple jobs and their related tasks. I want to know if it's possible to create a conditional formatting that says if the percentage complete under the "task" is under 100% and within X number of days of the target date, it's red/yellow/green.


So, if a target date is 70-100 days out; 0%-10% - RED, 11%-45% - YELLOW, 46%-100% - GREEN
If a target date is 40-69 days out; 0%-45% - RED, 46%-75% - YELLOW, 75%-100% - GREEN
If a target date is 20-40 days out; 0%-60% - RED, 61-90% - YELLOW, 91%-100% - GREEN
If a target date is 0-20 days out; 0%-85% - RED, 86%-95% - YELLOW, 96-100% - GREEN
If a target date has passed, Anything under 100% is RED.

Hopefully I've explained this well enough. Any help is greatly appreciated!

Start with a cell to figure out the number of days. Example: Cell G1 =F1-Today()
Then the conditional formatting will be easier using AND
Apply the Conditional formatting to your range (B5:Ex) (x is the last row number)
=And($G$1>70,B5<.11) Set format to Fill Red
=And($G$1>70,B5>.1,B5<.46) Set format to Fill Yellow
etc...

In other words you will have 9 conditional formats applied to the same range
They read like this: Format this cell if Fixed Cell G1 is greater than 70 AND Relative Cell is less that 11% the formatting will be fill the cell with red

Thanks Wayne! This appears to work for the most part - I still can't get the formatting to change colors based on the target date. If I go too far out, all formatting is lost. When I change the target date to tomorrow, none of the formatting changes. Any ideas what I'm doing wrong or what I need to add to do this?

again, thanks so much!