How to Auto-Populate Date in a Cell when its Adjacent Cell is Updated in Excel?

Let us assume we have a situation where we want to know the date of the last update of a value in a list, and you want to record the date of the last update. This process can be done using the VBA application, as it cannot be completed directly with formulas. Read this tutorial to learn how you can populate date in a cell when its adjusting cell is updated in Excel.

AutoPopulate Date in a Cell when its Adjacent Cell is Updated

Here we will insert VBA code into the sheet to complete the task. Let us see a simple process to understand how we can auto-populate dates in cells when adjusting cells in Excel using the VBA code.

Step 1

Let us consider that we have a list of names similar to the data shown in the below image.

Now to open the VBA application, right-click on the sheet name and select view code, then click on insert, and then type the programme into the textbox as shown in the below image.

Example

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updated by Nirmal
   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

In the code, "B:B" represents the data in cell "B", and "-1" describes the update date in the cell to its left; if we change it to "1", the date will be displayed in cell "C".

Step 2

Now save the workbook as a VBA enabled template, then close the VB application with the command Alt + Q, and the date will be auto-inserted in cell A whenever we update the value in cell B, as shown in the image below.

Conclusion

In this tutorial, we used a simple example to demonstrate how we can auto-populate dates in cells when their adjacent cells are updated in Excel.

Pradeep Kumar

Updated on: 11-Jan-2023

833 Views

Kickstart Your Career

Get certified by completing the course

Get Started