In this article, I will show you how to auto-populate date in Excel when a cell is updated. I will show the whole process using Excel VBA. I will also show how you can modify the code for your specific purposes.
Sometimes several people work on a worksheet and anyone can update the worksheet anytime. So, the worksheet owner might want to see the last updates with the update date and time.
Table of Contents
- Auto-populating dates in cells using Excel VBA (for the whole column)
- Auto-populating dates in some specific cells (not the entire column)
- If your developer tab is not showing
- Download Working File
Auto-populating dates in cells using Excel VBA (for the whole column)
Take a look at the following dynamic image. You see when I put some value in a cell of column A, adjacent column B cell show the date and time I am changing the cell.
VBA Code and step by step procedure
Private Sub Worksheet_Change(ByVal Target As Range) ' Auto Date Dim Cell As Range For Each Cell In Target If Cell.Column = Range("A:A").Column Then If Cell.Value <> "" Then Cells(Cell.Row, "B").Value = Now Else Cells(Cell.Row, "B").Value = "" End If End If Next Cell End Sub
This is the VBA code that I am going to use. Let’s show you where to put this code.
1) Click on the Developer tab. And then click on the Visual Basic command in the Code window.
2) Microsoft VBA code editor will open like the following image. Keyboard shortcut to open this window is ALT + F11.
3) Double click on the worksheet on which you want to apply our code. So, you will find an editor look like this.
4) Click the drop-down part of the (General) and choose Worksheet.
5) In the same way, from the right-side tab, choose Change option. You will find that two codes are appearing in the code editor. Select and delete the lower part code.
6) Now insert the code below between these lines:
Private Sub Worksheet_Change(ByVal Target As Range)
‘ Insert code here
' Auto Date Dim Cell As Range For Each Cell In Target If Cell.Column = Range("A:A").Column Then If Cell.Value <> "" Then Cells(Cell.Row, "B").Value = Now Else Cells(Cell.Row, "B").Value = "" End If End If Next Cell
7) We are done. Now save your workbook pressing CTRL + S or from the File tab. You will see the following Microsoft message. This message will show if your workbook is saved with .xlsx extension instead of .xlsm extension. Your workbook now has VBA code, so it cannot be saved with .xlsx extension.
Select No as we want to save the workbook as a macro-enabled file type.
8) Choose Excel Macro-Enabled Workbook (.xlsm) option from the Save as type menu and save the file newly.
So, this is the whole procedure to auto-populate a cell when a change occurs in the adjacent cell.
Now the big question. How will you use this code for your specific purpose?
Say you want to use this code for another worksheet and for specific cell ranges. Like the image below.
You want to use this code for VBA2 worksheet and for the columns C and D.
Here are the steps:
1) Open the Visual Basic editor and you might find that already the previous code is showing while the previous worksheet is selected.
2) Double click on the worksheet on which you want to apply your VBA code. On the right side, choose Worksheet on the left and on the right choose the Change option from the drop-down.
3) Now use the following code:
' Auto Date Dim Cell As Range For Each Cell In Target If Cell.Column = Range("C:C").Column Then If Cell.Value <> "" Then Cells(Cell.Row, "D").Value = Now Else Cells(Cell.Row, "D").Value = "" End If End If Next Cell
I am showing the places where you will make the changes.
Auto-populating dates in some specific cells (not the entire column)
Look at the following spreadsheet. Say, in this worksheet, people can only update data on cell range D3: D22 and adjacent cells on column E will record the date of last changes.
1) Use the following code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count > 1 Then Exit Sub If Not Intersect(Range("D3:D22"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub
2) Here is the result
This code will impact only the cell range D3: E22. Not any cell beyond this range.
If your developer tab is not showing
1) At first, check out whether your worksheet is showing the Developer tab in the tab list.
No developer tab is showing in the following image.
2) Click on the File tab.
3) Now choose Options from the left side menu.
4) Excel Options dialog box will appear. Choose Customize Ribbon and then on the right side of the dialog box, you see the Developer option is unchecked.
5) Check the box and click OK on the dialog box.
6) You see, now the Developer tab is showing in the tab list.
Download Working File
So, these are two pieces of codes that you can use for your specific purposes. Do you know any better way to auto-populate dates in Excel when cells are updated? Let me know in the comment box.