Forum Discussion

Copper Contributor

Jun 14, 2017

comparing dates in 2-columns and highlighting the cell

Hi Everyone, I am comparing the two Dates in the two columns (D and E). The Dates in column D are source Dates and the Dates in column E are Start date of the Project. I am calculating the differe...

Macros & VBA

Need Help

JKPieterse

Silver Contributor

Jun 14, 2017

Something like:

Sub dateCompare()

zLastRow = Range("D" & Rows.Count).End(xlUp).Row    'last data row

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For r = 2 To zLastRow

If Cells(r, "E").Value = "" Then

zColour = vbRed
zText = "Project not started"

Else
zWeeks = (Cells(r, "E") - Cells(r, "D")) / 7        'date difference in weeks

Select Case zWeeks
Case Is > 4                                         'later than 4 weeks
zColour = vbRed
zText = "Project delayed " & Int(zWeeks) & " weeks"
Case 2 To 4                                         'between 2 and 4 weeks
zColour = vbYellow
zText = "Project ongoing"
Case Is < 2                                         'less than 2 weeks
zColour = vbGreen
zText = "Project On-Time"

Case Else                                           'in case of duff data..
zColour = xlNone
zText = " check dates"
End Select

Cells(r, "D").Interior.Color = zColour              'set cell background colour
Cells(r, "F") = zText                               'set project status
End If
Next
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

End Sub