Good morning! Yes, there is a way to automatically track the last modified date of a cell, column, or row in Excel. You can use the built-in function
"NOW()" to capture the current date and time, and then use a simple
VBA code to update the
"Last modified date" column whenever a cell in the corresponding row is changed.
Here are the steps to set this up:
- Open your Excel worksheet and select the column where you want to track the last modified date.
- Right-click on the column header and choose "Insert" to add a new column next to it. This will be your "Last modified date" column.
- In the first cell of the "Last modified date" column (B2 in your example), enter the formula "=IF(A2<"",NOW(),"")". This will display the current date and time whenever the corresponding cell in column A is not empty.
- Press "Enter" to apply the formula to the cell.
- Select the cell with the formula (B2) and copy it.
- Select the entire "Last modified date" column (B2:B7 in your example) and paste the formula by pressing "Ctrl+V".
- Now, whenever you change a cell in column A, the corresponding cell in column B will update with the current date and time.
To make this process automatic, you can use a
VBA code that will run whenever a cell in column A is changed. Here's how:
1. Press
"Alt+F11" to open the Visual Basic Editor.
2. In the
Project Explorer window, double-click on the worksheet where you want to track the changes.
3. In the code window that opens, paste the following code:
Formula:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Target.Offset(0, 1).Value = Now()
End If
End Sub
4. Save the workbook and close the Visual Basic Editor.
Now, whenever you change a cell in column A, the corresponding cell in column B will update with the current date and time automatically, without you having to type anything.