Excel VBA Worksheet_Change代码无报错失效，求修复：组合键求和标红

首先，你的原代码存在几个关键逻辑错误，导致它完全不生效，我来一步步拆解问题并给出修复方案：

原代码的核心问题 #

1. 范围引用错误：cell.Range("d5:d50") 是相对于cell的相对引用，不是工作表的绝对D5:D50范围，这会导致你引用的区域完全偏离目标。
2. 条件判断无效：If (cell.Range("d5:d50").Value) & ... 只是拼接了一个区域的值，但没有和任何内容做比较，这个条件永远不会成立，所以后续代码根本不会执行。
3. 求和逻辑错误：你直接对整个M5:M50求和，而不是按D、E、H的组合分组求和，完全不符合需求。

修复后的基础版本代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim targetRange As Range
    Dim i As Long, j As Long
    Dim currentCombo As String
    Dim sumVal As Double
    
    ' 设置当前工作表
    Set ws = Me
    ' 定义需要监控的区域：D5:D50、E5:E50、H5:H50、M5:M50
    Set targetRange = Union(ws.Range("D5:D50"), ws.Range("E5:E50"), ws.Range("H5:H50"), ws.Range("M5:M50"))
    
    ' 只处理目标区域内的修改，避免无关操作
    If Intersect(Target, targetRange) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    On Error GoTo Cleanup ' 确保事件能恢复，防止意外禁用
    
    ' 先清除所有M列单元格的背景色，避免旧颜色残留
    ws.Range("M5:M50").Interior.ColorIndex = xlColorIndexNone
    
    ' 遍历5到50行，按组合分组计算
    For i = 5 To 50
        ' 如果当前行的M列未被处理，开始计算对应组合的总和
        If ws.Range("M" & i).Interior.ColorIndex = xlColorIndexNone Then
            currentCombo = ws.Range("D" & i).Value & ws.Range("E" & i).Value & ws.Range("H" & i).Value
            sumVal = ws.Range("M" & i).Value ' 初始化当前组的总和
            
            ' 查找同组合的其他行并累加总和
            For j = i + 1 To 50
                If ws.Range("D" & j).Value & ws.Range("E" & j).Value & ws.Range("H" & j).Value = currentCombo Then
                    sumVal = sumVal + ws.Range("M" & j).Value
                End If
            Next j
            
            ' 如果总和大于100，为当前组所有M列单元格设置红色背景
            If sumVal > 100 Then
                For j = i To 50
                    If ws.Range("D" & j).Value & ws.Range("E" & j).Value & ws.Range("H" & j).Value = currentCombo Then
                        ws.Range("M" & j).Interior.Color = RGB(255, 0, 0)
                    End If
                Next j
            End If
        End If
    Next i

Cleanup:
    Application.EnableEvents = True
    If Err.Number <> 0 Then MsgBox "错误：" & Err.Description
End Sub

代码说明 #

• 范围限制：通过Intersect判断修改的单元格是否在目标区域内，避免不必要的计算。
• 分组求和：先遍历每一行获取D、E、H的组合值，再查找所有同组合的行计算总和，确保只对相同组合的M列值求和。
• 颜色处理：先清除所有M列的背景色，再根据求和结果为符合条件的组设置红色背景，避免旧颜色残留。
• 错误处理：加入On Error GoTo Cleanup确保即使出错，Excel事件也能恢复启用，防止事件被意外禁用导致后续操作失效。

高效优化版本（适合数据量较大的场景） #

如果你的数据接近50行，推荐使用Dictionary来存储组合值对应的总和和行号，减少嵌套循环次数，提升效率：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim targetRange As Range
    Dim i As Long
    Dim comboDict As Object
    Dim key As Variant
    Dim sumVal As Double
    Dim rowNums As Variant
    Dim num As Variant
    
    Set ws = Me
    Set targetRange = Union(ws.Range("D5:D50"), ws.Range("E5:E50"), ws.Range("H5:H50"), ws.Range("M5:M50"))
    If Intersect(Target, targetRange) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    On Error GoTo Cleanup
    
    Set comboDict = CreateObject("Scripting.Dictionary")
    ws.Range("M5:M50").Interior.ColorIndex = xlColorIndexNone
    
    ' 第一步：遍历所有行，将组合值、对应总和和行号存入字典
    For i = 5 To 50
        currentCombo = ws.Range("D" & i).Value & ws.Range("E" & i).Value & ws.Range("H" & i).Value
        If comboDict.Exists(currentCombo) Then
            ' 累加总和，追加行号
            comboDict(currentCombo) = Array(comboDict(currentCombo)(0) + ws.Range("M" & i).Value, comboDict(currentCombo)(1) & "," & i)
        Else
            ' 首次添加组合，存储初始总和和当前行号
            comboDict(currentCombo) = Array(ws.Range("M" & i).Value, CStr(i))
        End If
    Next i
    
    ' 第二步：遍历字典，为符合条件的组合设置背景色
    For Each key In comboDict.Keys
        sumVal = comboDict(key)(0)
        If sumVal > 100 Then
            rowNums = Split(comboDict(key)(1), ",")
            For Each num In rowNums
                ws.Range("M" & num).Interior.Color = RGB(255, 0, 0)
            Next num
        End If
    Next key

Cleanup:
    Application.EnableEvents = True
    If Err.Number <> 0 Then MsgBox "错误：" & Err.Description
    Set comboDict = Nothing
End Sub

这个版本只需要遍历两次数据（一次统计分组，一次上色），比嵌套循环的效率更高，尤其适合数据量较多的情况。

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