Excel VBA: Add a Last Modified Timestamp in Column P When Column G Changes
Question details
The user needs to record the date and time in Column P whenever the corresponding cell in Column G is edited.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A worksheet should automatically track the last modification time for each row based on changes made in Column G.
- Observed behavior
- A normal worksheet formula cannot create a permanent timestamp because formulas recalculate; a worksheet-change event or Office Script is required.
Save the workbook as a macro-enabled .xlsm file and confirm that macros are allowed. Decide whether clearing a value in Column G should also clear its corresponding timestamp in Column P.
Use a worksheet-change VBA event to timestamp Column P
Use this solution when the timestamp must be written automatically and remain fixed until the related cell in Column G changes again.
Place the code in the worksheet module that contains the data, not in a standard VBA module. The event handles single-cell and multi-cell edits and temporarily disables events so writing to Column P does not trigger the macro again.
In Excel, press Alt+F11 to open the Visual Basic Editor, then double-click the worksheet containing your data under Microsoft Excel Objects.
Choose Worksheet from the left procedure list and Change from the right procedure list, then replace or complete the generated procedure with this code: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo CleanUp If Intersect(Target, Me.Columns("G")) Is Nothing Then Exit Sub Application.EnableEvents = False Dim cell As Range For Each cell In Intersect(Target, Me.Columns("G")) If cell.Row >= 2 Then If Len(cell.Value) > 0 Then Me.Cells(cell.Row, "P").Value = Now Me.Cells(cell.Row, "P").NumberFormat = "m/d/yyyy h:mm AM/PM" Else Me.Cells(cell.Row, "P").ClearContents End If End If Next cell CleanUp: Application.EnableEvents = True End Sub
Save the file as an Excel Macro-Enabled Workbook (*.xlsm), return to the worksheet, edit a cell such as G2, and verify that the matching cell P2 receives the current date and time.
Create automatic edit timestamps with WPS Spreadsheet VBA
WPS Spreadsheet can use a worksheet-change macro to monitor Column G and write a fixed date and time to the matching row in Column P. Its familiar spreadsheet interface and support for common Microsoft Office formats make it straightforward to migrate and test this workflow.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, open the workbook, and save a macro-enabled copy if the file contains VBA.
- 2. Open the VBA editor: Use the Developer tab or the VBA editor shortcut available in your WPS installation, then open the worksheet module for the data sheet.
- 3. Add the worksheet-change event: Insert the Worksheet_Change code that checks Column G, writes Now to the matching cell in Column P, and disables events during the write.
- 4. Enable macros and test the trigger: Allow macros when prompted, edit a cell in Column G, and confirm that the corresponding cell in Column P displays the current timestamp.
Frequently Asked Questions
Why can’t a formula permanently timestamp a change in Column G?
A standard formula recalculates whenever the workbook recalculates, so it cannot reliably preserve the original edit time. Use the Worksheet_Change VBA event to write a fixed value to Column P.
Where should the VBA code be placed?
Place the code in the specific worksheet module under Microsoft Excel Objects, such as Sheet1, rather than in a standard module. This allows the Worksheet_Change event to respond to edits on that sheet.
What happens when multiple cells in Column G are pasted at once?
The For Each loop processes every changed cell in the affected range and writes a separate timestamp to the matching row in Column P.
How do I stop Column P from being cleared when Column G is emptied?
Remove the Else block containing Me.Cells(cell.Row, "P").ClearContents. The timestamp will then remain in Column P even after the related Column G cell is cleared.
Why does the macro stop responding after an error?
The CleanUp section restores Application.EnableEvents to True. If events were previously disabled, open the Immediate window in the VBA editor and run Application.EnableEvents = True, then test the worksheet again.
