How to auto populate date in cell when adjacent cell is updated in Excel?
Sometimes, when update a cell in a certain column, you may want to mark the latest date about the updating. This article will recommend a VBA method to solve this problem. When cell is updated, the adjacent cell will be auto populated with current date immediately.
Supposing the data you need to update locates in column B, and when cell in column B is updated, the current date will be populated in the adjacent cell of column A. See screenshot:
You can run the following VBA code to solve this problem.
1. Right click the sheet tab you need to auto populate date based on the adjacent updated cell, and then click View Code from the right-clicking menu.
2. In the Microsoft Visual Basic for Applications window, please copy and paste the below VBA code into the Code window.
VBA code: auto populate current date in a cell when the adjacent cell is updated
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Updated by Extendoffice 2017/10/12 Dim xRg As Range, xCell As Range On Error Resume Next If (Target.Count = 1) Then If (Not Application.Intersect(Target, Me.Range("B:B")) Is Nothing) Then _ Target.Offset(0, -1) = Date Application.EnableEvents = False Set xRg = Application.Intersect(Target.Dependents, Me.Range("B:B")) If (Not xRg Is Nothing) Then For Each xCell In xRg xCell.Offset(0, -1) = Date Next End If Application.EnableEvents = True End If End Sub
3. Press Alt + Q keys at the same time to close the Microsoft Visual Basic for Applications window.
From now on, when updating cells in column B, the adjacent cell in column A will be populated with current date immediately. See screenshot:
- How to auto populate other cells when selecting values in Excel drop down list?
- How to autocomplete when typing in Excel drop down list?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!