Bernie,

I note your point about making the calculation happen, but I don't mind if
it updates on document open, close, or save. However, I probably will add the
=Now() function at a later date. However, I'm still having trouble getting
this to work.

I've kept my original worksheet change event to apply the formatting based
on status and I've added a worksheet calculate event to change it according
to date, as follows below.

The problem is when I calculate it the whole sheet turns yellow.

I have tested this by swapping the if statements around and have found out
that this is happening because the macro is applying all the if statements
i.e. the final statement makes the interior yellow.

Private Sub Worksheet_Calculate()
Dim myC As Range
Dim WatchRange1 As Range

Application.ScreenUpdating = False

Set WatchRange1 = Range("G2:G1000")

For Each myC In WatchRange1

If myC.Cells.Value < Now And myC.Offset(0, -3).Value = "Statement" Then
Range(myC, myC.Offset(0, -6)).Interior.ColorIndex = 15 'Grey
Range(myC, myC.Offset(0, -6)).Font.ColorIndex = 34 'light blue
Else
Range(myC, myC.Offset(0, -6)).Interior.ColorIndex = 34 'light blue
Range(myC, myC.Offset(0, -6)).Font.ColorIndex = 0 'black
End If

If myC.Value < Now And myC.Offset(0, -3).Value = "Closed" Then
Range(myC, myC.Offset(0, -6)).Interior.ColorIndex = 15 'light grey
Range(myC, myC.Offset(0, -6)).Font.ColorIndex = 16 'dark grey
End If

If myC.Value < Now And myC.Offset(0, -3).Value = "Open" Then
Range(myC, myC.Offset(0, -6)).Font.ColorIndex = 3 'red
Range(myC, myC.Offset(0, -6)).Interior.ColorIndex = 0 'clear
Else
Range(myC, myC.Offset(0, -6)).Font.ColorIndex = 0 'black
Range(myC, myC.Offset(0, -6)).Interior.ColorIndex = 0 'clear
End If

If myC.Value < Now And myC.Offset(0, -3).Value = "" Then
Range(myC, myC.Offset(0, -6)).Font.ColorIndex = 0 'clear
Range(myC, myC.Offset(0, -6)).Interior.ColorIndex = 0 'Black
Else
Range(myC, myC.Offset(0, -6)).Interior.ColorIndex = 0 'clear
Range(myC, myC.Offset(0, -6)).Font.ColorIndex = 0 'black
End If

If myC.Value < Now And myC.Offset(0, -3).Value = "Pending" Then
Range(myC, myC.Offset(0, -6)).Font.ColorIndex = 3 'red
Range(myC, myC.Offset(0, -6)).Interior.ColorIndex = 36 'yellow
Else
Range(myC, myC.Offset(0, -6)).Interior.ColorIndex = 36 'yellow
Range(myC, myC.Offset(0, -6)).Font.ColorIndex = 0 'black
End If

Next myC

Application.ScreenUpdating = True
End Sub

'--
Kind regards
Dylan


"Bernie Deitrick" wrote:

The calculate event will work when the sheet calculates - which is when the date values would
change. You need to have at least one volatile function on the worksheet to force the calculate -
putting =NOW() into a cell would make the event work.