conditional formatting for blank cells dependent on due dates

[Brandi_____]

Help!! I need to create a formula for a due date spreadhseet for multiple items with varying due dates. I have the due dates, but I want to highlight the cells directly below based on the following:

If the cell date is equal to or less than the above due date, GREEN.

If the cell is BLANK and the due date above is less than or equal to TODAY, RED
If the cell is BLANK and the due date above is between TODAY +1 and TODAY +7, ORANGE
If the cell is BLANK and the due date above is between TODAY +8 and TODAY +14, YELLOW
If the cell is BLANK and the due date above is greater than or equal to greater than TODAY +15, NO COLOR

[Special-K]

Select the cell (lets assume A2)

Conditional Formatting
New Rule
Use a formula to determine...
4 formulas required, in this order.

=AND(A2 < > "",A2 < A1)
format as green

=(AND(A2="",A1 > =TODAY()+8,A1 < =TODAY()+14))
format as yellow

=(AND(A2="",A1 > =TODAY()+1,A1 < =TODAY()+7))
format as orange

=(AND(A2="",A1 < =TODAY()))
format as red

Format as required
Use Format painter (paintbrush icon) to copy to other cells

[SamuelMRoth]

For some reason, I can't load attach the file here, but I think I have your solution.

Use separate conditional formatting for each color, and use formulas to determine the cells to format.
For this example, have your due date in cell B3, and the coloring cell will be B4:

For the red formatting, use this formula:
=AND(B4=0,B3-TODAY()<=0)

For the green formatting, use this formula:
=AND(B4<>0,B4<=B3)

For the orange formatting, use this formula:
=AND(B4=0,(B3-TODAY())<=7)

For the yellow formatting, use this formula:
=AND(B4=0,((B3-TODAY())<=14))

If you would like to provide your email address, I can email the file to you since I can't upload the file to this thread.

[Brandi_____]

Worked like a charm! Thank you!