I want to know if there is a formula to update a cell in Excel with today's date

• when a user changes any value in the row that contains that cell

OR

• when a user changes the value of 6 / 7 / 8 specific cells (which happen to be on the same row).

Getting today's date is fairly easy using A1 = today(), but how do I get this to happen on user update as described above?

5

1 Answer

This is an example for row#7. Enter the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range) Dim R As Range Set R = Range("A7").EntireRow If Intersect(R, Target) Is Nothing Then Exit Sub Application.EnableEvents = False R.Cells(1, 1).Value = Date Application.EnableEvents = True
End Sub

If anything is changed in row#7, A7 will be updated with today's date.

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

and

(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

Macros must be enabled for this to work!

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy