Conditional Formatting

[Guest]

I have a spreadsheet with documents that are overdue. I want column D, which
contains the contract number for that doucment, to be highlighted when the
due date in column Q has past.

However, there are three different colours I want column D to be
highlighted, red if over 2 weeks overdue, orange if 1-2 weeks overdue and
yellow if 0-1 week overdue.

I would appreciate any help on this one!!

Cheers
Nic

 

[Guest]

Sub dt()
lr = Cells(Rows.Count, 17).End(xlUp).Row
For i = 1 To lr
x = Date
y = Range("Q" & i).Value
If x - y <= 7 Then
Range("D" & i).Interior.ColorIndex = 6
ElseIf x - y >= 8 And x - y <= 14 Then
Range("D" & i).Interior.ColorIndex = 12
ElseIf x - y >= 15 Then
Range("D" & i).Interior.ColorIndex = 3
End If
Next
End Sub

 

[Joerg]

If your date is in Q1, then ConditionalFormat D1 as follows:

"formula is" for condition 1:
=Q1<NOW()-14

"formula is" for condition 2:
=AND(Q1>=NOW()-14,Q1<NOW()-7)

"formula is" for condition 3:
=AND(Q1>=NOW()-7,Q1<NOW())

For each condition set the color as needed.

Cheers,

Joerg Mochikun