Excel：VLOOKUP联动单元格更新时，如何用VBA清除对应状态单元格？

问题根源 #

你之前使用的Worksheet_Change事件仅在手动修改单元格内容或直接编辑公式时触发，而VLOOKUP这类公式因外部数据变化导致的单元格值更新属于计算结果变化，不会触发Change事件，因此代码失效。要监听公式计算后的数值变化，需改用Worksheet_Calculate事件，同时需要记录单元格历史值来对比是否发生变动。

解决方案代码 #

右键目标工作表标签→选择「查看代码」，将以下代码粘贴到弹出的代码模块中：

' 模块级变量，存储C列历史值，用于对比变化
Private oldCValues As Variant

Private Sub Worksheet_Activate()
    ' 激活工作表时初始化C列历史值（默认从第4行开始，可按需调整）
    Dim lastRow As Long
    lastRow = Me.Cells(Me.Rows.Count, "C").End(xlUp).Row
    If lastRow >= 4 Then
        oldCValues = Me.Range("C4:C" & lastRow).Value
    End If
End Sub

Private Sub Worksheet_Calculate()
    Dim currentCValues As Variant
    Dim lastRow As Long
    Dim i As Long
    
    ' 关闭事件触发，避免循环执行
    Application.EnableEvents = False
    On Error GoTo Cleanup
    
    ' 获取当前C列数据范围
    lastRow = Me.Cells(Me.Rows.Count, "C").End(xlUp).Row
    If lastRow < 4 Then GoTo Cleanup ' 无数据直接退出
    
    currentCValues = Me.Range("C4:C" & lastRow).Value
    
    ' 对比新旧值，发现变化则清除对应行E列内容
    For i = 1 To UBound(currentCValues, 1)
        ' 处理空值，避免类型不匹配错误
        Dim oldVal As String, currentVal As String
        oldVal = IIf(IsEmpty(oldCValues(i, 1)), "", CStr(oldCValues(i, 1)))
        currentVal = IIf(IsEmpty(currentCValues(i, 1)), "", CStr(currentCValues(i, 1)))
        
        If oldVal <> currentVal Then
            ' 数组索引1对应第4行，因此行号为i+3
            Me.Range("E" & i + 3).ClearContents
        End If
    Next i
    
    ' 更新历史值为当前值，用于下次对比
    oldCValues = currentCValues

Cleanup:
    Application.EnableEvents = True
End Sub

代码说明 #

• oldCValues：模块级变量，保存C列上一次的数值，每次计算后更新基准值。
• Worksheet_Activate：切换到该工作表时初始化历史值，确保第一次对比的基准正确。
• Worksheet_Calculate：每次工作表计算完成后触发，逐行对比C列新旧值，发现变动则清除对应行的E列内容。
• 若你的C列数据起始行不是第4行，需修改代码中C4、i+3的数字（比如起始行是2，改为C2:C"&lastRow、i+1）。

注意事项 #

1. 保存文件时需选择.xlsm格式（启用宏的工作簿），否则宏会丢失。
2. 代码已兼容外部表格关闭时VLOOKUP返回错误的情况。
3. 若C列数据量极大，循环对比可能有轻微卡顿，可缩小监控范围（比如只指定特定行）。

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