[Excel VBA] How to Automatically Enter Today’s Date in Column A When Column B Is Changed (Using Worksheet_Change)

2025年12月30日

Background

In daily work, I often wanted to “automatically record the date in column A when a value is entered in column B.”

I thought this would be useful for managing work dates or input dates, so I learned how to automate this recording process using VBA.

Goal

When something is entered in column B.
Automatically enter today’s date (Date) in the corresponding cell in column A.

VBA Code to Use

You can achieve this using the Worksheet_Change event. Paste the following code into the code window of the target sheet (e.g., Sheet1).

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Target, Me.Range("B:B")) ' Limit to changes in Column B

    If rng Is Nothing Then Exit Sub

    Application.EnableEvents = False ' Prevent infinite loop

    Dim cell As Range
    For Each cell In rng
        If cell.Value <> "" Then
            cell.Offset(0, -1).Value = Date ' Enter date in Column A
        End If
    Next cell

    Application.EnableEvents = True
End Sub

Note: How this code works

It targets only column B.
When a value is entered in column B, “Today’s Date (Date function)” is entered in column A of the same row.
Simply entering a blank space will not change column A.
Application.EnableEvents = False is used to prevent recursive calls of the event (omitting this may cause an infinite loop).

Application Examples

Automatic management of daily reports or data entry history.
Using it as a timestamp for input work.
Note: If you want to clear the date when the value is deleted, you can add logic using If cell.Value = "" Then.