RainbowStorm
10-18-2022, 09:26 AM
I am looking to apply a timestamp, and automatically delete the row when it is 4 years old. I can make it work to automatically delete the row within a 24 time period, but I cannot set larger periods or I am presented with a type mismatch.
This is the code I was using
Private Sub Worksheet_Activate()
Dim stampcell As Range
Dim TLSh As Worksheet
Set TLSh = Worksheets("Deselected Time Log")
Application.ScreenUpdating = False
For Each stampcell In TLSh.Range("A2:X300")
If Now > stampcell.Value2 + TimeValue("23:59:59") Then
Me.Range(stampcell.Address).ClearContents
stampcell.ClearContents
End If
Next stampcell
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim stampcell As Range
Dim TLSh As Worksheet
Application.EnableEvents = False
Set TLSh = Worksheets("Deselected Time Log") "timestamp sheet
Set myRng = Range("A2:X300")
If Not Intersect(Target, myRng) Is Nothing Then
TLSh.Range(Target.Address).Value2 = Now
TLSh.Range(Target.Address).NumberFormat = "MM/DD/YYYY HH:MM:SS"
End If
Application.EnableEvents = True
End Sub
I identified the issue was quantifying it as time value so attempted to change it to the following:
If Now > stampcell.Value2 + DateAdd("d", 1, stampcell.Value2) Then
This was so I could test it before changing it to 6 years.
Is there anyway for me to do this?
This is the code I was using
Private Sub Worksheet_Activate()
Dim stampcell As Range
Dim TLSh As Worksheet
Set TLSh = Worksheets("Deselected Time Log")
Application.ScreenUpdating = False
For Each stampcell In TLSh.Range("A2:X300")
If Now > stampcell.Value2 + TimeValue("23:59:59") Then
Me.Range(stampcell.Address).ClearContents
stampcell.ClearContents
End If
Next stampcell
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim stampcell As Range
Dim TLSh As Worksheet
Application.EnableEvents = False
Set TLSh = Worksheets("Deselected Time Log") "timestamp sheet
Set myRng = Range("A2:X300")
If Not Intersect(Target, myRng) Is Nothing Then
TLSh.Range(Target.Address).Value2 = Now
TLSh.Range(Target.Address).NumberFormat = "MM/DD/YYYY HH:MM:SS"
End If
Application.EnableEvents = True
End Sub
I identified the issue was quantifying it as time value so attempted to change it to the following:
If Now > stampcell.Value2 + DateAdd("d", 1, stampcell.Value2) Then
This was so I could test it before changing it to 6 years.
Is there anyway for me to do this?