SOLVED

How do I set a date to update only when certain cells update in Excel?

%3CLINGO-SUB%20id%3D%22lingo-sub-1618888%22%20slang%3D%22en-US%22%3EHow%20do%20I%20set%20a%20date%20to%20update%20only%20when%20certain%20cells%20update%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1618888%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%20I%20found%20a%20question%20like%20this%20from%202019%20with%20no%20replies.%20Hopefully%20someone%20can%20answer%20this%20for%20me.%20I%20want%20to%20insert%20a%20date%20that%20updates%20if%20certain%20cells%20in%20that%20row%20update.%20I%20do%20not%20want%20this%20date%20to%20update%20whenever%20the%20sheet%20is%20open%20or%20variables%20elsewhere%20recalculate%2C%20which%20I%20believe%20Now()%20and%20Today()%20do.%20Do%20I%20need%20to%20write%20a%20function%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1618888%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1618931%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20set%20a%20date%20to%20update%20only%20when%20certain%20cells%20update%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1618931%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F774681%22%20target%3D%22_blank%22%3E%40StaceyDale%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20you%20will%20indeed%20need%20a%20Macro%20for%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3C%2FP%3E%3CP%3EIf%20Target.Address%20%3D%20%22%24A%241%22%20Then%3CBR%20%2F%3ERange(%22B1%22).Value%20%3D%20Date%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20above%20macro%20will%20input%20today's%20date%20in%20cell%20B1%20when%20ever%20a%20change%20is%20made%20on%20cell%20A1.%3C%2FP%3E%3CP%3EYou%20can%20add%20a%20bunch%20of%20IF%20statement%20for%20every%20cell%20you%20want%20to%20monitor%20and%20every%20cells%20you%20want%20the%20date%20to%20be%20inputted%20to.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1619755%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20set%20a%20date%20to%20update%20only%20when%20certain%20cells%20update%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1619755%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703724%22%20target%3D%22_blank%22%3E%40Bennadeau%3C%2FA%3E%26nbsp%3B%20Thank%20you!%20I%20used%20this%2C%20since%20I%20wanted%20to%20use%20two%20columns%20continuously.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20WorkRng%20As%20Range%3CBR%20%2F%3EDim%20Rng%20As%20Range%3CBR%20%2F%3EDim%20xOffsetColumn%20As%20Integer%3CBR%20%2F%3ESet%20WorkRng%20%3D%20Intersect(Application.ActiveSheet.Range(%22B%3AB%22%2C%20%22C%3AC%22)%2C%20Target)%3CBR%20%2F%3EIf%20WorkRng.Column%20%3D%202%20Then%3CBR%20%2F%3ExOffsetColumn%20%3D%20-1%3CBR%20%2F%3EIf%20Not%20WorkRng%20Is%20Nothing%20Then%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3EFor%20Each%20Rng%20In%20WorkRng%3CBR%20%2F%3EIf%20Not%20VBA.IsEmpty(Rng.Value)%20Then%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).Value%20%3D%20Now%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).NumberFormat%20%3D%20%22dd-mm-yyyy%2C%20hh%3Amm%3Ass%22%3CBR%20%2F%3EElse%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).ClearContents%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EIf%20WorkRng.Column%20%3D%203%20Then%3CBR%20%2F%3ExOffsetColumn%20%3D%20-2%3CBR%20%2F%3EIf%20Not%20WorkRng%20Is%20Nothing%20Then%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3EFor%20Each%20Rng%20In%20WorkRng%3CBR%20%2F%3EIf%20Not%20VBA.IsEmpty(Rng.Value)%20Then%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).Value%20%3D%20Now%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).NumberFormat%20%3D%20%22dd-mm-yyyy%2C%20hh%3Amm%3Ass%22%3CBR%20%2F%3EElse%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).ClearContents%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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?

 

2 Replies
Highlighted
Best Response confirmed by StaceyDale (New Contributor)
Solution

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 If

End 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. 

Highlighted

@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 If

If 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