Excel Worksheet_Change事件报错：清除指定范围外单元格时类型不匹配

问题根源 #

原代码报错主要来自三个核心问题：

1. Intersect判断逻辑错误：直接用Not Intersect(...)完全无效——Intersect返回的是Range对象，判断是否相交必须用Is Nothing，直接对Range对象取逻辑非会触发类型不匹配错误，尤其是操作监控范围外的单元格时。
2. 多单元格操作未处理：当Target是多个单元格（比如选中一片区域删除），Target.Value <> ""会报错，因为多单元格的Value是数组，无法直接和字符串比较。
3. 错误处理位置混乱：原代码的错误捕获放在If块内部，会导致逻辑分支异常，甚至可能导致事件被永久禁用。

修正后的代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 定义需要监控的D列和E列指定区域
    Dim monitorDRange As Range
    Dim monitorERange As Range
    Set monitorDRange = Me.Range("D8:D31,D44:D67,D80:D103,D116:D139")
    Set monitorERange = Me.Range("E8:E31,E44:E67,E80:E103,E116:E139")
    
    ' 先禁用事件，防止修改单元格时循环触发Change事件
    Application.EnableEvents = False
    
    ' 临时捕获错误，避免因意外情况导致事件无法恢复启用
    On Error Resume Next
    
    ' 处理D列监控区域的单元格变更
    If Not Intersect(Target, monitorDRange) Is Nothing Then
        Dim cell As Range
        ' 遍历每个发生变更的单元格，支持多单元格操作
        For Each cell In Intersect(Target, monitorDRange)
            If cell.Value <> "" Then
                ' 写入日期戳到F列，时间戳到G列
                cell.Offset(0, 2).Value = Format(Now(), "mm-dd-yyyy")
                cell.Offset(0, 3).Value = Format(Now(), "hh:mm:ss")
            Else
                ' 如果单元格被清空，同步清除对应的日期和时间戳
                cell.Offset(0, 2).ClearContents
                cell.Offset(0, 3).ClearContents
            End If
        Next cell
    End If
    
    ' 处理E列监控区域的单元格变更
    If Not Intersect(Target, monitorERange) Is Nothing Then
        For Each cell In Intersect(Target, monitorERange)
            If cell.Value <> "" Then
                ' 写入日期戳到F列，时间戳到G列
                cell.Offset(0, 1).Value = Format(Now(), "mm-dd-yyyy")
                cell.Offset(0, 2).Value = Format(Now(), "hh:mm:ss")
            Else
                cell.Offset(0, 1).ClearContents
                cell.Offset(0, 2).ClearContents
            End If
        Next cell
    End If
    
    ' 恢复事件启用，必须确保这行执行，否则后续Change事件会失效
    Application.EnableEvents = True
    ' 恢复默认错误处理机制
    On Error GoTo 0
End Sub

关键改动说明 #

• 修正相交判断逻辑：用Not Intersect(Target, [监控区域]) Is Nothing准确判断目标单元格是否在监控范围内，彻底解决类型不匹配问题。
• 支持多单元格操作：通过遍历每个变更的单元格，处理批量修改或删除场景，避免数组值比较的错误。
• 优化事件控制：将Application.EnableEvents放在最外层，结合错误捕获，确保无论代码是否出错，事件都能恢复启用，避免Excel事件功能失效。
• 补充清空同步逻辑：当监控单元格被清空时，自动清除对应的日期和时间戳，保持数据的一致性。

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