Worksheet event to change formatting when date expires.
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.
HTH,
Bernie
MS Excel MVP
"DDawson" wrote in message
...
Thanks Bernie,
I tried the calculate method, but it doesn't apply until I save the
document. I wanted something that takes immediate effect.
However, I was able to cut and paste your additions into my code and replace
"myC" with "Target" to make the Change Event work.
=AND($G23<NOW(),$D23="Statement") - Grey fill and light blue font colour
If Target.Offset(0, 3).Value < Now And Target.Value = "Statement" Then
Range(Target.Offset(0, -3), Target.Offset(0, 3)).Interior.ColorIndex =
15
Range(Target.Offset(0, -3), Target.Offset(0, 3)).Font.ColorIndex = 34
End If
Kind Regards
Dylan
"Bernie Deitrick" wrote:
DD,
You can use the calculate event: see code below.
HTH,
Bernie
MS Excel MVP
Private Sub Worksheet_Calculate()
Dim WatchRange As Range
Dim myC As Range
Set WatchRange = Range("D1:D1000") 'change to suit
For Each myC In WatchRange
Select Case myC.Value
Case "Pending"
'Target.Interior.ColorIndex = 5 (To Colour the single Cell)
' Target.EntireRow.Interior.ColorIndex = 36 (To Colour the entire row
Range(myC.Offset(0, -3), myC.Offset(0, 3)).Interior.ColorIndex = 36
'(For columns A:G)
Range(myC.Offset(0, -3), myC.Offset(0, 3)).Font.ColorIndex = 0
Case "Statement"
Range(myC.Offset(0, -3), myC.Offset(0, 3)).Interior.ColorIndex = 34
Range(myC.Offset(0, -3), myC.Offset(0, 3)).Font.ColorIndex = 0
Case "Closed"
Range(myC.Offset(0, -3), myC.Offset(0, 3)).Interior.ColorIndex = 15
Range(myC.Offset(0, -3), myC.Offset(0, 3)).Font.ColorIndex = 16
Case "Open"
Range(myC.Offset(0, -3), myC.Offset(0, 3)).Interior.ColorIndex = 0
Range(myC.Offset(0, -3), myC.Offset(0, 3)).Font.ColorIndex = 0
Case ""
Range(myC.Offset(0, -3), myC.Offset(0, 3)).Interior.ColorIndex = 0
Range(myC.Offset(0, -3), myC.Offset(0, 3)).Font.ColorIndex = 0
End Select
' =AND($G23<NOW(),$D23="Statement") - Grey fill and light blue font colour
If myC.Offset(0, 3).Value < Now And myC.Value = "Statement" Then
Range(myC.Offset(0, -3), myC.Offset(0, 3)).Interior.ColorIndex = 15
Range(myC.Offset(0, -3), myC.Offset(0, 3)).Font.ColorIndex = 34
End If
' =AND($G23<NOW(),$D23<"Closed") - Red font colour
' wasn't sure what background you wanted....
If myC.Offset(0, 3).Value < Now And myC.Value < "Closed" Then
Range(myC.Offset(0, -3), myC.Offset(0, 3)).Interior.ColorIndex = 15
Range(myC.Offset(0, -3), myC.Offset(0, 3)).Font.ColorIndex = 3
End If
Next myC
Application.ScreenUpdating = True
End Sub
"DDawson" wrote in message
...
I have a macro to trigger conditional formatting events in my worksheet,
based on the cell text content in column D.
I also need an event that will update the formatting if the date in column G
is less than now, i.e. when it expires.
I have tried the following as conditional formatting, but I cannot copy the
formats down the columns, because I will lose all the existing formatting,
based on column D.
=AND($G23<NOW(),$D23="Statement") - Grey fill and light blue font colour
=AND($G23<NOW(),$D23<"Closed") - Red font colour
My existing macro is as follows; but, I doubt a change event will work,
because I am not actually changing the contents of the cells containing the
dates. Is there a way to trigger a worksheet event to update the formatting
when the date in column G expires?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count 1 Then Exit Sub
Application.ScreenUpdating = False
'If Target = "" Then Exit Sub (See Case "")
CellVal = Target
Set WatchRange = Range("D1:D1000") 'change to suit
If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Pending"
'Target.Interior.ColorIndex = 5 (To Colour the single Cell)
' Target.EntireRow.Interior.ColorIndex = 36 (To Colour the entire row
Range(Target.Offset(0, -3), Target.Offset(0, 3)).Interior.ColorIndex = 36
'(For columns A:G)
Range(Target.Offset(0, -3), Target.Offset(0, 3)).Font.ColorIndex = 0
Case "Statement"
Range(Target.Offset(0, -3), Target.Offset(0, 3)).Interior.ColorIndex = 34
Range(Target.Offset(0, -3), Target.Offset(0, 3)).Font.ColorIndex = 0
Case "Closed"
Range(Target.Offset(0, -3), Target.Offset(0, 3)).Interior.ColorIndex = 15
Range(Target.Offset(0, -3), Target.Offset(0, 3)).Font.ColorIndex = 16
Case "Open"
Range(Target.Offset(0, -3), Target.Offset(0, 3)).Interior.ColorIndex = 0
Range(Target.Offset(0, -3), Target.Offset(0, 3)).Font.ColorIndex = 0
Case ""
Range(Target.Offset(0, -3), Target.Offset(0, 3)).Interior.ColorIndex = 0
Range(Target.Offset(0, -3), Target.Offset(0, 3)).Font.ColorIndex = 0
End Select
End If
Application.ScreenUpdating = True
End Sub
|