HI Guys,
I have inhere a VBA code for updates tracker. But my problem is that it logs all the changes in all cells.
I want to have it log specific changes for specific column only.
I want it to just log the changes for column B.
Can someone correct or help me with this codes?
Option Explicit
Const intUsernameColumn = 1
Const intCellRefColumn = 2
Const intNewValueColumn = 3
Const intTimestampColumn = 4
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Application.EnableEvents = False
Cells(Target.Row, 1).Value = Date + Time
Application.EnableEvents = True
End If
Dim shtLog As Worksheet
Dim cll As Variant
Dim lngNextRow As Long
Set shtLog = ThisWorkbook.Sheets("Log")
For Each cll In Target.Cells
lngNextRow = shtLog.Cells.Find(What:="*", After:=[A1], Searchorder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
shtLog.Cells(lngNextRow, intUsernameColumn).Value = Environ("username")
shtLog.Cells(lngNextRow, intCellRefColumn).Value = cll.Address
shtLog.Cells(lngNextRow, intNewValueColumn).Value = cll.Value
shtLog.Cells(lngNextRow, intTimestampColumn).Value = Format(Now, "dd-mmm-yy hh:mm:ss")
Next cll
End Sub
Thank You in advance.
I have inhere a VBA code for updates tracker. But my problem is that it logs all the changes in all cells.
I want to have it log specific changes for specific column only.
I want it to just log the changes for column B.
Can someone correct or help me with this codes?
Option Explicit
Const intUsernameColumn = 1
Const intCellRefColumn = 2
Const intNewValueColumn = 3
Const intTimestampColumn = 4
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Application.EnableEvents = False
Cells(Target.Row, 1).Value = Date + Time
Application.EnableEvents = True
End If
Dim shtLog As Worksheet
Dim cll As Variant
Dim lngNextRow As Long
Set shtLog = ThisWorkbook.Sheets("Log")
For Each cll In Target.Cells
lngNextRow = shtLog.Cells.Find(What:="*", After:=[A1], Searchorder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
shtLog.Cells(lngNextRow, intUsernameColumn).Value = Environ("username")
shtLog.Cells(lngNextRow, intCellRefColumn).Value = cll.Address
shtLog.Cells(lngNextRow, intNewValueColumn).Value = cll.Value
shtLog.Cells(lngNextRow, intTimestampColumn).Value = Format(Now, "dd-mmm-yy hh:mm:ss")
Next cll
End Sub
Thank You in advance.