Automatically Update Date in Cell if Another Cell is Modified

Copper Contributor

I am not too familiar with coding in Excel but I'm hoping I can do this anyway.  How can I have an existing cell with a date automatically update to the current date but only if another cell in that row is changed?  Thanks in advance for your help. 

11 Replies

As an example:

Let's say you want to update the date in T2:T100 if any cell in the same row in columns A to M is changed.

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 the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("A2:M100"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("A2:M100"), Target).Rows
            Range("T" & rng.Row).Value = Date ' or Now if you want date+time
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

You can achieve this in Excel by using a simple VBA (Visual Basic for Applications) macro. Here's a step-by-step guide on how to set it up:

  1. Open the Excel Workbook:
    • Open the Excel workbook where you want to implement this functionality.
  2. Open the VBA Editor:
    • Press Alt + F11 to open the VBA editor.
  3. Insert a Module:
    • In the VBA editor, right-click on any item in the Project Explorer window and choose Insert -> Module. This will add a new module to your project.
  4. Paste the VBA Code:
    • Copy and paste the following VBA code into the module window:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim modifiedCell As Range
    Dim dateCell As Range

    ' Check if the changed cells are within the specified range
    If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
        ' Check if only one cell is changed
        If Target.Count = 1 Then
            ' Define the range of the date cell you want to update
            Set dateCell = Intersect(Target.EntireRow, Me.Range("B:B"))

            ' Check if the modified cell is not empty
            If Target.Value <> "" Then
                ' Update the date cell to the current date
                dateCell.Value = Date
            End If
        End If
    End If
End Sub

 

    • Make sure to modify the code to match your specific worksheet and cell references. In the example code, it's set up to monitor changes in column A and update the corresponding date in column B.
  1. Close the VBA Editor:
    • Close the VBA editor by clicking the "X" button or pressing Alt + Q.
  2. Save Your Workbook:
    • Save your workbook as a macro-enabled workbook with the extension .xlsm to keep the VBA code.

Now, whenever a cell in column A is modified, the corresponding date in column B will be automatically updated to the current date.

Remember, Excel will ask you to enable macros when you open the workbook. You need to enable them to make the code work. Additionally, make sure to keep a backup of your workbook before implementing VBA code, especially if you are not familiar with it.

Please see the insert file with the example.

The text and steps were edited with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

I currently have two other macros on the same worksheet - how does that work?  Also, I would like to have the date updated in column I if anything changes in column H.  Please note that the workbook constantly fluctuates as far as how many rows it has.

 

This is what I show currently:

 If Target.Column = 10 Then Cells(Target.Row, 8).FormulaR1C1 = "=Vlookup(RC[2],operation,2)"

If Target.Column = 10 Then Cells(Target.Row, 12).FormulaR1C1 = "=if(or(RC[-2] = 12,RC[-2]=6),10,if(or(RC[-2] = 7,RC[-2]=3,RC[-2]=8,RC[-2]=9,RC[-2]=10),9,1))"

End Sub

You enter a formula in column H if column J changes. That will happen each time you change a cell in column J - isn't that overkill. If J4 is changed 37 times, you'd enter the same formula in H4 37 times...

It also means that the user probably won't enter a value in column H directly, so the code that I posted won't work.

I am entering the data into field H & then I want it to know that if I change it, the current date needs to go into column I.

But the line

 

If Target.Column = 10 Then Cells(Target.Row, 8).FormulaR1C1 = "=Vlookup(RC[2],operation,2)"

 

enters a VLOOKUP formula in column 8 = column H. How does that work together with entering data in column H?

The existing two codes that are in there need to stay as they do something else.

Note: column H is the same as column 8 - the existing codes were put in by someone else

 

I'll have a look later on - no time now.

Ok.  No rush.  The spreadsheet did this previously but someone deleted the code or something in error last week.  I really appreciate any help that you can provide.

I tried using the original details you provided & I seem to have found something that is working.  Thanks again so much for all of your help.  Have a great day!