I rechecked this code and found it was not really working like you wanted it to so I worked it out again and came up with a code the keep tracks of all past values of the cell you want to check and it also record time of change and who changed it.
HERE IS THE NEW CODE
Public Sub Worksheet_Change(ByVal Target As Range)
Dim row_no As Integer, old_v As Variant
row_no = Sheets("Sheet3").Cells(1, 1).End(xlDown).Row 'number of the row of the last record
old_v = Sheets("Sheet3").Range("A" & row_no) 'Old value of designed cell
If Sheets("Sheet1").Range("A2") <> old_v Then
Sheets("Sheet3").Range("B" & row_no + 1) = Sheets("Sheet3").Range("B" & row_no) + 1 'Increment number of changes
Sheets("Sheet3").Range("A" & row_no + 1) = Sheets("Sheet1").Range("A2") 'Save actual value
Sheets("Sheet3").Range("C" & row_no + 1) = Now() 'record time of change
Sheets("Sheet3").Range("D" & row_no + 1) = Application.UserName 'record user name who changed the value
End If
End Sub
You have to set the Sheet3 the first time you start your counter. I mean delete all but the first 2 rows. Input the first and actual value of the cell and fill the other 3 columns.
You might want to hide the Sheet3 so no one will see or change it.
Bookmarks