I reviewed several post on using VBA to insert a time and date stamp when data in a row changed and implemented on that changed one column if anything in the row changed. I then tried modifying it to insert stamps in different locations to track different cells in the row. The result is the code below. However, it doesn't execute, and the code that changed just one cell if the row changed doesn't execute when I open that work book either.
I ran the procedures for MS14-082 but it had no effect but I am not sure it applied because there are no controls in this code.
Are there any problems with the code? If not what do you suggest for a fix?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range, d As Range, e As Range, f As Range, g As Range
For Each r In Target.Rows
If r.Row > 3 Then
If Intersect(Range("B:AS"), Target) Is Nothing Then Exit Sub
For Each c In Intersect(Range("B:L"), Target) 'If columns B Through L change record time in A
If Not IsEmpty(c) Then
Application.EnableEvents = False
Cells(c.Row, "A").Value = Format(Now, "DD-MMM-YY HH:MM")
Application.EnableEvents = True
End If
Next c
For Each d In Intersect(Range("S:W"), Target) 'If columns S Through W change record time in X
If Not IsEmpty(d) Then
Application.EnableEvents = False
Cells(d.Row, "X").Value = Format(Now, "DD-MMM-YY HH:MM")
Application.EnableEvents = True
End If
Next d
For Each e In Intersect(Range("Z:AD"), Target) 'If columns Z Through AD change record time in AE
If Not IsEmpty(e) Then
Application.EnableEvents = False
Cells(e.Row, "AE").Value = Format(Now, "DD-MMM-YY HH:MM")
Application.EnableEvents = True
End If
Next e
For Each f In Intersect(Range("AG:AK"), Target) 'If columns AG Through AK change record time in AL
If Not IsEmpty(f) Then
Application.EnableEvents = False
Cells(f.Row, "AL").Value = Format(Now, "DD-MMM-YY HH:MM")
Application.EnableEvents = True
End If
Next f
For Each g In Intersect(Range("AN:AR"), Target) 'If columns AN Through AR change record time in AS
If Not IsEmpty(g) Then
Application.EnableEvents = False
Cells(g.Row, "AS").Value = Format(Now, "DD-MMM-YY HH:MM")
Application.EnableEvents = True
End If
Next g
End If
End If
Next r
End Sub
I ran the procedures for MS14-082 but it had no effect but I am not sure it applied because there are no controls in this code.
Are there any problems with the code? If not what do you suggest for a fix?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range, d As Range, e As Range, f As Range, g As Range
For Each r In Target.Rows
If r.Row > 3 Then
If Intersect(Range("B:AS"), Target) Is Nothing Then Exit Sub
For Each c In Intersect(Range("B:L"), Target) 'If columns B Through L change record time in A
If Not IsEmpty(c) Then
Application.EnableEvents = False
Cells(c.Row, "A").Value = Format(Now, "DD-MMM-YY HH:MM")
Application.EnableEvents = True
End If
Next c
For Each d In Intersect(Range("S:W"), Target) 'If columns S Through W change record time in X
If Not IsEmpty(d) Then
Application.EnableEvents = False
Cells(d.Row, "X").Value = Format(Now, "DD-MMM-YY HH:MM")
Application.EnableEvents = True
End If
Next d
For Each e In Intersect(Range("Z:AD"), Target) 'If columns Z Through AD change record time in AE
If Not IsEmpty(e) Then
Application.EnableEvents = False
Cells(e.Row, "AE").Value = Format(Now, "DD-MMM-YY HH:MM")
Application.EnableEvents = True
End If
Next e
For Each f In Intersect(Range("AG:AK"), Target) 'If columns AG Through AK change record time in AL
If Not IsEmpty(f) Then
Application.EnableEvents = False
Cells(f.Row, "AL").Value = Format(Now, "DD-MMM-YY HH:MM")
Application.EnableEvents = True
End If
Next f
For Each g In Intersect(Range("AN:AR"), Target) 'If columns AN Through AR change record time in AS
If Not IsEmpty(g) Then
Application.EnableEvents = False
Cells(g.Row, "AS").Value = Format(Now, "DD-MMM-YY HH:MM")
Application.EnableEvents = True
End If
Next g
End If
End If
Next r
End Sub