Automatically adding the date/time to a cell when another cell is updated

Platinum Contributor

Dec 05, 2023

If you want the timestamp to be static and not update every day, you can use a combination of VBA (Visual Basic for Applications) and a worksheet event. The VBA code will be triggered whenever the specified cells are changed, and it will insert a timestamp in the cell below the updated cell.

Here's a step-by-step guide:

Press Alt + F11 to open the Visual Basic for Applications editor.
In the editor, right-click on VBAProject (Your Workbook Name) in the left pane.
Choose Insert -> Module to insert a new module.
Copy and paste the following VBA code into the module:

Vba code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your sheet name

    Dim updatedCell As Range
    Set updatedCell = Intersect(Target, ws.Range("A1:E1")) ' Adjust the range as needed

    If Not updatedCell Is Nothing Then
        Application.EnableEvents = False
        updatedCell.Offset(1, 0).Value = Now
        Application.EnableEvents = True
    End If
End Sub

5. Close the VBA editor.

Make sure to replace "Sheet1" with the actual name of your sheet, and adjust the range in the Intersect function to match the range of cells you want to monitor.

Now, whenever any cell in the specified range is updated, the cell below it will be populated with the current date and time. The timestamp will not automatically update daily; it will remain static once entered.

CJudd

Occasional Reader

Jan 12, 2026

NikolinoDE

Sorry to dig this things' grave, but it's just too useful for me right now.

I used this script, and it works as advertised, but I am having an issue when it interacts with another script I am using in the same workbook, but on a different sheet.

So my first bit of code (shown below) takes a row of date and moves it to the second sheet when I check the Closed box. I was hoping that it would move it to the second sheet, and your code would update the date when that happens. Unfortunately, it does not seem to recognize the event. Do you know of a way I could change your code here to recognized that event?

I did have to change your code to put the date in the cell to the right, simply changing the cell offset from (1, 0) to (0, 1), but that's the only code bit I changed.

First code to move rows to second sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngWatch As Range
    Dim rngChange As Range
    Dim c As Range
    Dim wsDest As Worksheet
    Dim rngDelete As Range
    
    'What column(s) are you monitoring for change?
    Set rngWatch = Range("C3:C300")
    'Where are we moving data to?
    Set wsDest = ThisWorkbook.Worksheets("Closed")
    
    'Did you change a cell of interest?
    Set rngChange = Intersect(rngWatch, Target)
    If rngChange Is Nothing Then Exit Sub
    
    'Prevent recursive calls and screen flicker
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    For Each c In rngChange
        If UCase(c.Value) = "TRUE" Then
             'Save this spot to clear later
            If rngDelete Is Nothing Then
                Set rngDelete = c
            Else
                Set rngDelete = Union(rngDelete, c)
            End If
            
            'Move the row
            With wsDest
                c.EntireRow.Copy Destination:=.Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            End With
        End If
    Next c
    
    'Remove the blank rows
    If Not rngDelete Is Nothing Then
        rngDelete.EntireRow.Delete
    End If
    
    'Reset
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
End Sub

YDN10

Copper Contributor

Dec 29, 2024

This sort of worked for me, however these are some changes I made to make this work:

I changed "Now" to "Date" to give me the date of the change.

I did not create a module, instead I right clicked the sheet of interest in the VBA code and clicked "view code" - which is where I subbed in the code you shared.

The updated code I used (specific to my excel):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Partners") ' Ensure this matches your sheet name.

    ' Check if the change occurred in Column F
    If Not Intersect(Target, ws.Columns("F")) Is Nothing Then
        Application.EnableEvents = False ' Disable events to avoid recursion

        Dim cell As Range
        For Each cell In Intersect(Target, ws.Columns("F"))
            ' Update the corresponding cell in Column G with the current date
            ws.Cells(cell.Row, "G").Value = Date
        Next cell

        Application.EnableEvents = True ' Re-enable events
    End If
End Sub

I used GPT to check and update it so hopefully it works as well as yours tomorrow... fingers crossed!

Forum Discussion

Automatically adding the date/time to a cell when another cell is updated

Resources