Tracking cells' changes

Question

Hi all, I'd like to fill in a cell automatically with the date and time of another call's update.

For example: when cell A2 is updated (any type of change), cell A3 is automatically updated with the date and time of cell A2's update.

Thank you!

Roberto

Answer 1

You literally asked "For example: when cell A2 is updated (any type of change), cell A3 is automatically updated"!

So the code that I wrote does that.

If you had asked "For example: when cell A2 is updated (any type of change), cell B2 is automatically updated", I would have written it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Range("A2:A10"), Target) ' Adjust the range as desired
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        rng.Offset(0, 1).Value = Now
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Answer 2

It works now!!!

Thanks a lot for your help and sorry for the wrong initial requirement.

See you!

Roberto

Answer 3

Thank you so much, Hans, it partially works.

Indeed, your script works on the rows instead of the columns: i.e. updating the cell A2, A3 is automatically filled in with date&time, instead of B2 as I would like).

Thank you for your support!

Roberto

Answer 4

This requires VBA, so it will only work in the desktop version of Excel for Windows and Mac.

Let's say you want to do this for cells A2:H2.

Format A3:H3 as date+time.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Range("A2:H2"), Target)
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        rng.Offset(1).Value = Now
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub