How to insert current timestamp when data in another cell changes in Excel?
Supposing you receive a worksheet which needs you to modify data in a certain column. And after modifying the worksheet, you need to insert the modified timestamp to let others know which cells have been changed. How to deal with it? This article will show you method of inserting current timestamp when data in another cell changes in Excel as below screenshot shown.
As below screenshot shown, you need to populate timestamp in column E if corresponding cells in column C are changed. Please do as follows.
1. In the worksheet you need to modify and mark with timestamp, right click the sheet tab and then click View Code from the right-clicking menu.
2. Then copy and paste the below VBA code into the Code window of the Microsoft Visual Basic for Applications window. See screenshot:
VBA code: Insert current timestamp when data in another cell changes
Private Sub Worksheet_Change(ByVal Target As Range) 'Updated by Extendoffice 20180830 Dim xCellColumn As Integer Dim xTimeColumn As Integer Dim xRow, xCol As Integer Dim xDPRg, xRg As Range xCellColumn = 3 xTimeColumn = 5 xRow = Target.Row xCol = Target.Column If Target.Text <> "" Then If xCol = xCellColumn Then Cells(xRow, xTimeColumn) = Now() Else On Error Resume Next Set xDPRg = Target.Dependents For Each xRg In xDPRg If xRg.Column = xCellColumn Then Cells(xRg.Row, xTimeColumn) = Now() End If Next End If End If End Sub
Note: In the code, number 3 means that you are going to modify data in column C, and 5 indicates the timestamp will be populated into column E. Please change them based on your needs.
3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.
From now on, when changing data in column C, the timestamp will be populated into corresponding cells in column E as below screenshot shown.
- How to insert or display last saved timestamp on worksheet in Excel?
- How to insert timestamp into specific cell when macro is run in Excel?