S
Sapphire69
Hello,
I am trying to modify a macro that I found in another post to work with my
worksheet and I'm woefully unsucessful.
What I need is a macro that, when run, will list all changes since the macro
was last run. The changes it will show are the username of the person who
made the change, the updated values, and the date the changes were made.
Here is the macro that I found in the other post:
Private Sub Worksheet_Change(ByVal Target As Range)
'Column to be watched
Const sWatch As String = "J"
'Column of reference data that will show on Track sheet
Const sRef As String = "A"
Dim rWatch As Range
Dim rCell As Range
Dim sUser As String
Dim lOffset As Long
Set rWatch = Intersect(Target, Columns(sWatch))
If rWatch Is Nothing Then Exit Sub
sUser = Environ("username")
lOffset = Columns(sRef).Column - Columns(sWatch).Column
With Worksheets("DUEDATE-CONT COMPLIANCE")
ActiveSheet.Unprotect
For Each rCell In rWatch
With .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Value = rCell.Offset(0, lOffset)
.Offset(0, 1).Value = Now
.Offset(0, 2).Value = sUser
.Offset(0, 3).Value = rCell.Value
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowDeletingColumns:=True,
AllowDeletingRows:= _
True, AllowSorting:=True, AllowFiltering:=True
End With
Next rCell
End With
End Sub
Can anyone out there help me?
I am trying to modify a macro that I found in another post to work with my
worksheet and I'm woefully unsucessful.
What I need is a macro that, when run, will list all changes since the macro
was last run. The changes it will show are the username of the person who
made the change, the updated values, and the date the changes were made.
Here is the macro that I found in the other post:
Private Sub Worksheet_Change(ByVal Target As Range)
'Column to be watched
Const sWatch As String = "J"
'Column of reference data that will show on Track sheet
Const sRef As String = "A"
Dim rWatch As Range
Dim rCell As Range
Dim sUser As String
Dim lOffset As Long
Set rWatch = Intersect(Target, Columns(sWatch))
If rWatch Is Nothing Then Exit Sub
sUser = Environ("username")
lOffset = Columns(sRef).Column - Columns(sWatch).Column
With Worksheets("DUEDATE-CONT COMPLIANCE")
ActiveSheet.Unprotect
For Each rCell In rWatch
With .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Value = rCell.Offset(0, lOffset)
.Offset(0, 1).Value = Now
.Offset(0, 2).Value = sUser
.Offset(0, 3).Value = rCell.Value
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowDeletingColumns:=True,
AllowDeletingRows:= _
True, AllowSorting:=True, AllowFiltering:=True
End With
Next rCell
End With
End Sub
Can anyone out there help me?