Modifying a Macro that Tracks Changes

[leooclaws]

I would like to modify a macro that I am using to track changes in a workbook, but do not know the best way to go about it. The macro that I am using is listed below.
As of right now, the changes tracker is giving me the name of the active worksheet and the cell number that was changed. The problem with that is, if I add or delete any rows, it is hard to go back and trace which row was changed. So, what I wanted to do was modify the macro so that in the cell changed column it would still add the active worksheet, the cell that was changed, and the column F that corresponds to the row.

I am trying to track changes made for bank accounts. So, all of the account numbers are always in column F. So Ideally, if I changed one of the signers from John Doe to Kevin Doe, the value that would show up on the changes tracker worksheet would be, “US Accounts: $H:$7:123456489”
A B C D E F G H
BRANCH BANK NAME ADDRESS COUNTRY ACCOUNT NAME ACCOUNT # CURRENCY SIGNERS
505 Citibank California USA Bank Account 1 123456489 USD John Doe Jane Doe



Does anyone know of a way to modify this so that I could get that information? Or, if anyone has a different macro that might work. Thanks in advance for any help!

Here is the macro that I am currently using:

Option Explicit

Dim vOldVal 'Must be at top of module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim bBold As Boolean


If Target.Cells.Count > 1 Then Exit Sub
If ActiveSheet.Name = "Pricing" Then Exit Sub

'On Error Resume Next

    With Application
         .ScreenUpdating = False
         .EnableEvents = False

    End With

    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
    bBold = Target.HasFormula
        With Sheets("Changes Tracker")
            '.Unprotect Password:="Secret"
                If .Range("A1") = vbNullString Then
                    .Range("A1:H1") = Array("Cell Changed", "Old Value", _
                        "New Value", "Old Formula", "New Formula", "Time of Change", "Date of Change", "User")
                End If

            With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
                  .Value = ActiveSheet.Name & " : " & Target.Address
                  .Offset(0, 1) = vOldVal
            With .Offset(0, 2)
              If bBold = True Then
                .ClearComments
                .AddComment.Text Text:= _
                     "OzGrid.com:" & Chr(10) & "" & Chr(10) & _
                        "Bold values are the results of formulas"

              End If
                .Value = Target
                .Font.Bold = bBold
                
            End With
                .Offset(0, 3) = Time
                .Offset(0, 4) = Date
                .Offset(0, 5) = Application.UserName
            End With
            .Cells.Columns.AutoFit
            '.Protect Password:="Secret"
        End With

    vOldVal = vbNullString

    With Application
         .ScreenUpdating = True
         .EnableEvents = True
    End With
On Error GoTo 0
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    vOldVal = Target
End Sub

Private Sub test()
    Application.EnableEvents = True
End Sub

[Fotis1991]

Welcome to the forum. Pls take some minutes to read forum rules because(in this case):

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)