Excel粘贴值时Worksheet_Change事件未触发的问题及解决方法

问题原因 #

你的代码开头有一行If Target.Count > 1 Then Exit Sub，当粘贴值到多个单元格时，Target会包含所有被粘贴的单元格，此时Target.Count必然大于1，代码会直接退出过程，导致后续逻辑完全没执行，看起来就像事件没触发——实际上Worksheet_Change事件是触发了的，只是被这行代码提前终止了。

解决方案 #

移除If Target.Count > 1 Then Exit Sub这行代码，改为遍历Target区域中的每一个单元格，逐个处理符合条件的单元格。同时优化代码结构，减少重复的区域判断逻辑，提升效率。

修改后的代码如下：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim targetRanges As Range
    Dim cell As Range
    Dim myRange As Range
    Dim r As Range
    Dim myRange2 As Range
    Dim s As Range
    
    ' 合并所有监控区域为一个Range对象，避免重复调用Union
    Set targetRanges = Union( _
        Sheet8.Range("$AP$46:$AP$145"), _
        Sheet8.Range("$BA$46:$BA$145"), _
        Sheet8.Range("$BL$46:$BL$145"), _
        Sheet8.Range("$BW$46:$BW$145"), _
        Sheet8.Range("$CH$46:$CH$145"), _
        Sheet8.Range("$CS$46:$CS$145"), _
        Sheet8.Range("$DD$46:$DD$145"), _
        Sheet8.Range("$DO$46:$DO$145"), _
        Sheet8.Range("$DZ$46:$DZ$145"), _
        Sheet8.Range("$EK$46:$EK$145") _
    )
    
    Application.EnableEvents = False
    
    ' 遍历每个被修改的单元格
    For Each cell In Target
        ' 仅处理在监控区域内的单元格
        If Not Intersect(cell, targetRanges) Is Nothing Then
            Set myRange = Sheet3.Range("A2:C1000")
            Set r = myRange.Find(What:=cell.Value, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
            
            If Not r Is Nothing Then
                cell.Offset(0, 2) = Application.IfError(Application.VLookup(cell.Value, myRange, 3, False), "")
                cell.Offset(0, 1) = Application.IfError(Application.VLookup(cell.Value, myRange, 2, False), "")
            End If
            
            Set myRange2 = Sheet1.Range("B2:B25")
            Set s = myRange2.Find(What:=cell.Value, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
            If s Is Nothing Then
                cell.Interior.Color = RGB(208, 208, 80)
            End If
        End If
        
        ' 处理单元格清空后的底色恢复
        If cell.Value = "" Then
            cell.Interior.Color = RGB(255, 255, 255)
        End If
    Next cell
    
    Application.EnableEvents = True
End Sub

额外优化说明 #

1. 合并监控区域为单个对象，减少重复操作，提升代码可读性和运行效率
2. 遍历Target内所有单元格，确保每个被粘贴的单元格都能被处理
3. 保留Application.EnableEvents的开关，避免代码修改单元格时触发循环事件

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