Apr 21 2022 02:56 PM
Hi All,
I would like a formula which when i Input an update in column F then the date updated column H should be automatically populated with that days date. Is this possible
Thank You
Apr 21 2022 03:50 PM
This requires VBA.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("F2:F" & Rows.Count), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Intersect(Range("F2:F" & Rows.Count), Target).Offset(0, 2).Value = Date
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
Apr 21 2022 04:07 PM
Apr 21 2022 04:09 PM
Apr 21 2022 04:12 PM
It will work for the entire column except for the cell in row 1 - I assumed that F1 would contain a header.
Each time you edit one or more cells in F2:F1048576, the corresponding cell(s) in column H will be set to the then current date.
Apr 21 2022 04:15 PM
Column F is mentioned explicitly in the code.
The cells that will contain the date are specified as an offset to this column: the .Offset(0, 2) in the code tells Excel to use the cells two columns to the right. If you want the dates in column C, i.e. 4 columns to the left of column F, use .Offset(0, -4)
Apr 22 2022 01:06 AM
@Hans Vogelaar this code is perfect thank you but when I delete something from column f the date still remains even though the cell is empty how do I get rid of the date if the cell is empty
Apr 22 2022 01:31 AM
This version will clear the date in column H if the cell in column F is cleared.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Range("F2:F" & Rows.Count), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each rng In Intersect(Range("F2:F" & Rows.Count), Target)
If rng.Value = "" Then
rng.Offset(0, 2).ClearContents
Else
rng.Offset(0, 2).Value = Date
End If
Next rng
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub