Forum Discussion

StaceyDale's avatar
StaceyDale
Copper Contributor
Aug 28, 2020
Solved

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 th...
excel
  • Bennadeau's avatar
    Bennadeau
    Aug 28, 2020

    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. 

Bennadeau's avatar
Bennadeau
Iron Contributor
Aug 28, 2020

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. 

  • StaceyDale's avatar
    StaceyDale
    Copper Contributor
    Aug 28, 2020

    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

     

Resources