当指定单元格变更时清除对应行单元格内容（多行批量需求）

Got it, let's tackle this problem so you don't have to write repetitive code for each row. The goal is to trigger a clear action on columns D-F for specific rows (2, 6, 10... up to 118) whenever the corresponding B column cell changes, with each row acting independently.

Step-by-Step Implementation #

1. Open the VBA Editor: Press Alt + F11 in Excel to launch the editor.
2. Select Your Target Worksheet: In the Project Explorer pane (left side), double-click the worksheet where you want this functionality to work (e.g., Sheet1).
3. Paste the Following Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Disable events temporarily to prevent infinite loops when clearing cells
    Application.EnableEvents = False
    
    ' Check if the changed cell is a single cell (not a range)
    If Target.Cells.Count = 1 Then
        ' Verify the cell is in column B and the row is in your target set (2,6,10...118)
        If Target.Column = 2 And (Target.Row - 2) Mod 4 = 0 And Target.Row <= 118 Then
            ' Clear columns D to F in the same row
            Me.Cells(Target.Row, "D").Resize(1, 3).ClearContents
        End If
    End If
    
    ' Re-enable events so future changes trigger the macro
    Application.EnableEvents = True
End Sub

Key Code Explanations #

• Worksheet_Change Event: This built-in event runs automatically whenever any cell in the worksheet is modified.
• Application.EnableEvents = False: Stops the macro from triggering itself when we clear cells (since clearing counts as a change). We re-enable it at the end to keep future changes working.
• (Target.Row - 2) Mod 4 = 0: This formula checks if the row is part of your sequence: subtract 2 from the row number, if dividing by 4 leaves no remainder, it's one of your target rows (2, 6, 10... 118).
• Resize(1, 3): Takes the D column cell in the target row and expands the selection to include the next 2 columns (E and F), then clears their contents.

Important Notes #

• Save your workbook as an Excel Macro-Enabled Workbook (.xlsm) to preserve the VBA code.
• Test by changing a value in B2, B6, etc.—you should see D-F in that row clear immediately.
• Each row operates independently, so changes in one target row won't affect others.

内容的提问来源于stack exchange，提问作者Nicholas F.