Excel VBA需求：根据Status列值自动插入对应日期至指定列

Got it, let's sort out this Excel automation task for you. Here's a straightforward, step-by-step solution using VBA that fits your exact needs:

First, open your Excel workbook and press Alt + F11 to launch the VBA Editor. Alternatively, you can go to the Developer tab (if it's visible) and click Visual Basic.

In the VBA Editor's Project Explorer (left pane), find the worksheet where your data is stored (it'll be named something like Sheet1 or whatever you've renamed it). Double-click that worksheet to open its code window, then paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Prevent infinite loop from triggering the change event again
    Application.EnableEvents = False
    
    ' Exit if multiple cells are changed at once (e.g., bulk paste)
    If Target.Cells.Count > 1 Then GoTo Cleanup
    
    ' Check if the changed cell is in the Status column (Column C)
    If Target.Column = 3 Then
        Select Case Target.Value
            Case "IQ Pending"
                ' Insert today's date in IQ Created Date column (Column D)
                Target.Offset(0, 1).Value = Date
            Case "Waiting for manager response"
                ' Insert today's date in Sent mail to manager date column (Column E)
                Target.Offset(0, 2).Value = Date
            Case Else
                ' Optional: Clear dates if status is changed to other options
                ' Target.Offset(0, 1).ClearContents
                ' Target.Offset(0, 2).ClearContents
        End Select
    End If
    
Cleanup:
    ' Re-enable events
    Application.EnableEvents = True
End Sub

Let's break down what this code does:

• Worksheet_Change event triggers automatically whenever any cell in the worksheet is edited.
• Application.EnableEvents = False stops the code from triggering itself when it writes the date (prevents an infinite loop).
• We check if the edited cell is in Column C (your Status column) and only act if it's a single cell change.
• Using Select Case, we match the selected Status value:
  • If it's IQ Pending, we write today's date (Date function) to the cell immediately to the right (Column D, IQ Created Date).
  • If it's Waiting for manager response, we write today's date two columns to the right (Column E, Sent mail to manager date).
• The optional commented lines let you clear the dates if the user switches to another Status (like Inbound delay or Others) — just remove the ' to enable that.

Go back to your Excel worksheet, select a cell in the Status column, use the dropdown to pick IQ Pending — you'll see the current date pop up in the IQ Created Date column. Pick Waiting for manager response, and the date will appear in the Sent mail to manager date column. Perfect!

内容的提问来源于stack exchange，提问作者Sunny Jayadev