Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
Dim rng As Range
Dim dteToday As Date
Dim dteTargetDate As Date
Set rng = Intersect(Range("D2:D" & Rows.Count & ",J2:J" & _
Rows.Count & ",O2:O" & Rows.Count), Target)
If Not rng Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
rng.Font.ColorIndex = xlColorIndexAutomatic
rng.Interior.ColorIndex = xlColorIndexNone
dteToday = Range("F1").Value
For Each cel In rng
If IsDate(cel.Value) Then
dteTargetDate = cel.Value
If dteTargetDate Then
If dteTargetDate < dteToday Then
cel.Font.ColorIndex = 4 ''Green
Else
cel.Font.ColorIndex = 0
End If
If dteTargetDate + 75 <= dteToday Then
cel.Interior.ColorIndex = 6 ''Yellow
Else
cel.Interior.ColorIndex = 0 ''White
End If
End If
Else
EntryError = MsgBox("Incorrect Date Entry", 0)
End If
Next cel
End If
Cells(2, 1).Select
Application.EnableEvents = True
Application.ScreenUpdating = True
Cells(2, 1).Activate
End Sub
1. cel is dimensioned but doesn't appear to be set anywhere.
2. For Each cel In rng in this statement this seemed to me to address each cel (not set yet) but in reality it only addresses the changed cell.
What am I missing? Is there a better way to write this?