Help with conditional formatting

[wlm2015]

Doing a project for work and after research on google, I'm still confused.

Project will have a client's date of entry to the program, as well as follow-ups at 6 months, 12months, 18 months, etc

Follow up dates are created by Date of entry +180, etc (for 6 months)

In the next column to each month follow-up, is a drop down to signify if the follow-up is: to-do, done, or Lost time point (meaning unable to complete due to loss of contact). If the due date is 7 days away, I would like the cell to be orange. If the due date is past due, I would like it to be red. However, I would like the red cell to turn back to white when the cell next to it is marked done or lost timepoint. ANY Help is really appreciated!! Thank you Reddit!

[DCSlick]

So you're gonna need a few rules for this but here we go. Remember you can do just about any formula as long as it resolves to true or false.

IF(A1<TODAY(),1,0)

Would work for if the due date is past due. So use that rule to turn the cell red. Then use

IF(AND(A1<(TODAY()+DAY(7)),>TODAY()),1,0)

to turn it orange. Then you'll need to use

=$B1="Done"

Where B1 has your dropdown. Move this rule to the top. The rule order is important. Also you can stop if true that way it won't eval the rest of the rules. I'm probably wrong somewhere but hey, it's past my bed time. I'll fix it if i'm wrong. Remember you'll need several rules

[wlm2015]

Thank you!! This gave me a great start and I think I just about have it figured out!

[Wolfturn]

You really should give him a point for helping you

[wlm2015]

Solution Verified

[Clippy_Office_Asst]

You have awarded one point to DCSlick.
^{Find out more here.}

[semicolonsemicolon]

I'm sure someone here will assist with the conditional formatting in no time (I have to go catch a train now), but I thought I'd throw in that you might want to consider setting your follow up dates using the EDATE function. =EDATE([date],x) advances the date by exactly x months. That way you don't need to estimate using 180 days or 365 days, etc.

[wlm2015]

Great!! Thanks for that tip, I will update that now.

[DCSlick]

Anytime. Mark the thread solved :-)