VBA & Macro Problems

Keep a Static Timestamp When an Excel Cell Changes

Adam DavisAdam Davis Nov 18, 2026 868 views

Question details

Record a static date and time in column A when a value is entered in column J, preserve it when the value is edited, and clear it when the value is deleted.

Keep a Static Timestamp When an Excel Cell Changes
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A worksheet uses column J for entries and column A to record when each entry was first added.
Observed behavior
Each column A timestamp should remain unchanged while its column J entry is edited and disappear when that entry is cleared.
Before you start

Open the workbook in desktop Excel and save it as an Excel Macro-Enabled Workbook (.xlsm). Make sure column A is reserved for timestamps and that existing entries in column J already have timestamps in column A if they need to be preserved.

Solution 1Recommended

Use a worksheet change event to maintain timestamps

Use this VBA event to create and clear timestamps automatically for edits or pasted values in column J.

The macro uses an empty cell in column A to identify an entry that needs a timestamp. It does not check the previous value in column J, so manually clearing a timestamp in column A can cause a new timestamp to be created on the next edit.

The event responds to direct worksheet changes, including multi-cell pastes. It does not run when a formula result changes only because the workbook recalculates.

1
Open the worksheet code

In desktop Excel, right-click the relevant worksheet tab and select View Code. If the worksheet already has a Worksheet_Change procedure, incorporate the logic into that procedure rather than adding a second one.

2
Add the event macro

Paste this code into the worksheet's code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim changedCells As Range Dim cell As Range Dim isBlank As Boolean Set changedCells = Intersect(Target, Me.Columns("J")) If changedCells Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False For Each cell In changedCells.Cells If IsError(cell.Value2) Then isBlank = False Else isBlank = (Len(CStr(cell.Value2)) = 0) End If If isBlank Then Me.Cells(cell.Row, "A").ClearContents ElseIf IsEmpty(Me.Cells(cell.Row, "A").Value) Then Me.Cells(cell.Row, "A").Value = Now Me.Cells(cell.Row, "A").NumberFormat = "yyyy-mm-dd hh:mm:ss" End If Next cell CleanUp: Application.EnableEvents = True End Sub

3
Save and test

Save the .xlsm workbook and enable macros if prompted for a trusted file. Enter a value in a blank J cell, edit it, and then delete it; check that the corresponding A cell is timestamped, remains unchanged during the edit, and is cleared on deletion.

Use a worksheet change event to maintain timestamps
Keep timestamp cells consistent: The macro clears column A when a corresponding J cell is cleared, so do not store unrelated data there. If a populated J cell has no timestamp, its next edit will create one rather than recover the original entry time.
Free Microsoft Office alternative

Work with Excel-compatible spreadsheets in WPS Office

WPS Office is a free, lightweight alternative for everyday spreadsheet work. Its familiar interface and Excel format compatibility make it easy to move existing files; the Excel-specific VBA event above is not a guaranteed fix in WPS.

Open and edit spreadsheets in a familiar interfaceCompatible with Microsoft Excel formats such as XLSX and XLSReview timestamp columns alongside other worksheet dataFree and lightweight for everyday office tasks
microsoft office alternative - wps office

Frequently Asked Questions

Where should I paste the timestamp macro?

Right-click the worksheet tab in desktop Excel, select View Code, and paste the procedure into that worksheet's code window, not a standard module.

Will editing an existing value in column J change its timestamp?

No. The macro writes a timestamp only when the corresponding column A cell is empty. It preserves an existing timestamp when the J value is edited.

Does the macro work when I paste several cells into column J?

Yes. The procedure loops through all changed cells in column J, setting or clearing the corresponding column A cells as needed.

Why is the timestamp not changing when a formula recalculates?

Worksheet_Change responds to direct cell changes, not recalculation alone. A formula-driven timestamp requires a different approach, such as a carefully designed calculation event.

How do I keep the macro when I save the workbook?

Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm). Enable macros only when you trust the workbook.