Need help probably VBA, tracking changes in cells

[Prophecy]

I'm new to VBA and need help in creating log of changes or tracker of status,
On first sheet I have smth like this:

Status	Username
NY	Richard
LA	Max

If i change status on the first sheet i want to get written on the second sheet automatically get written info like this:

Date	Status	Username
11/22/2024	LA	Richard
12/28/2024	NY	Richard
15/22/2024	LA	Max

Could anyone help me with this?

 

[Micron]

I seemed to recall that long ago we had the ability to track changes. That probably bloats a workbook but if you want to look into the idea or into co-authoring it might be an option for you.

Track changes in a Shared Workbook - Microsoft Support

Use Track Changes to track, maintain, and display information about the changes that are made to a shared workbook.

support.microsoft.com

 

[Prophecy]

Ended up with this code

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next

    Dim logSheet As Worksheet
    Dim changeRange As Range
    Dim newRow As Integer

    ' Set the reference to the log sheet
    Set logSheet = Sheets("LogSheet")

    ' Set the range to monitor for changes
    Set changeRange = Sheets("Sheet1").Range("A2:A500")

    ' Check if the change occurred in the monitored range
    If Not Intersect(Target, changeRange) Is Nothing Then
        ' Find the next empty row in the log sheet
        newRow = logSheet.Cells(logSheet.Rows.Count, "A").End(xlUp).Row + 1

        ' Record the date, status, and username in the log sheet
        logSheet.Cells(newRow, 1).Value = Date
        logSheet.Cells(newRow, 2).Value = Sheets("Sheet1").Cells(Target.Row, 1).Value
        logSheet.Cells(newRow, 3).Value = Sheets("Sheet1").Cells(Target.Row, 2).Value
    End If

    On Error GoTo 0
End Sub

 

[Micron]

If you expect to be able to log more than 3267 change rows you will need to make newRow a Long. Integer has somewhere around that limit.

I'm not seeing the value in the results but I might be misinterpreting things. I see you having the changed value in column A being written to the log sheet, so you'll have NY in both sheets if that was what the value was changed to. With that code, the only way you can know what the value was before is if its prior value is in the log sheet, but there would have to be some way of knowing that log row 5 (NY) got changed to LA and that you can pick this out when LA is written to e.g. row 12 of the log sheet. You will also add to the log when A column cell has nothing in it and you type in anything at all and leave the cell. If you delete a value in A you'll enter the date in A of the log sheet, then have a blank cell in column B. Don't know if I'm making any sense here.

 

[kevin9999]

Another option...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Application.EnableEvents = False
        Target.Resize(1, 2).Copy Worksheets("LogSheet").Cells(Rows.Count, 2).End(xlUp).Offset(1)
        Worksheets("LogSheet").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = Date
        Application.EnableEvents = True
    End If
End Sub