Forum Discussion
How do I set a date to update only when certain cells update in Excel?
Hello all, I found a question like this from 2019 with no replies. Hopefully someone can answer this for me. I want to insert a date that updates if certain cells in that row update. I do not want this date to update whenever the sheet is open or variables elsewhere recalculate, which I believe Now() and Today() do. Do I need to write a function?
Hi StaceyDale,
I believe you will indeed need a Macro for this.
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1").Value = Date
End IfEnd Sub
The above macro will input today's date in cell B1 when ever a change is made on cell A1.
You can add a bunch of IF statement for every cell you want to monitor and every cells you want the date to be inputted to.
2 Replies
Hi StaceyDale,
I believe you will indeed need a Macro for this.
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1").Value = Date
End IfEnd Sub
The above macro will input today's date in cell B1 when ever a change is made on cell A1.
You can add a bunch of IF statement for every cell you want to monitor and every cells you want the date to be inputted to.
Bennadeau Thank you! I used this, since I wanted to use two columns continuously.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B", "C:C"), Target)
If WorkRng.Column = 2 Then
xOffsetColumn = -1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End IfIf WorkRng.Column = 3 Then
xOffsetColumn = -2
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End If
End Sub
