VBA - Code That Will Log Cell Change Record By A Range And Log It

[tzcoding]

Situation: I have this VBA code that logs any change that is made in the excel sheet and that great; but i need it modified. The plan is to use this in one column as a master change log. But i need a sepret one that will log the window user ID / DATE / Time and lock it to the ROW they edited. So if i change column A the info will be logged in column B.

Private Sub Worksheet_Change(ByVal Target As range)
    '***************************************************************************************
    'purpose:   This will log any changes to the excel sheet in coulmn AG
    'Last Rev: 03/20/2023
    '***************************************************************************************
  If Not Intersect(Target, range("A:AF")) Is Nothing Then
    With Application
    .EnableEvents = False
    Cells(Rows.Count, "AG").End(xlUp).Offset(1).Value = .UserName & " has modified column " & Split(Target.Address, "$")(1) & " row " & Target.Cells(1, 1).Row & " at " & Format(Now, "dd-mm-yyyy hh:mm AM/PM")
    .EnableEvents = True
    End With
  End If
End Sub

 

[Legacy 143009]

This code will work. But what if someone changes column B?

Private Sub Worksheet_Change(ByVal Target As range)
    '***************************************************************************************
    'purpose:   This will log any changes to the excel sheet in coulmn AG
    'Last Rev: 03/20/2023
    '***************************************************************************************
  If Not Intersect(Target, range("A:AF")) Is Nothing Then
    With Application
    .EnableEvents = False
    Cells(Rows.Count, Target.Column+1).End(xlUp).Offset(1).Value = .UserName & " has modified column " & Split(Target.Address, "$")(1) & " row " & Target.Cells(1, 1).Row & " at " & Format(Now, "dd-mm-yyyy hh:mm AM/PM")
    .EnableEvents = True
    End With
  End If
End Sub