Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet event to change formatting when date expires.
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date expires in 2 years | Excel Worksheet Functions | |||
Updating a last modified date using the worksheet change event | Excel Programming | |||
Conditional formatting or worksheet change event? | Excel Programming | |||
Message box when date expires | Excel Programming |