基于其他单元格内容格式化指定单元格时遇运行时错误'9'求助

错误根源 #

• Selection对象滥用：代码里同时操作.Cells(i,3)和Selection，但Selection是当前手动选中的单元格，和循环处理的单元格大概率不是同一个，当选中区域没有条件格式时，Selection.FormatConditions.Count为0，访问索引为0的条件格式会触发下标越界（VBA里条件格式索引从1开始）。
• 条件格式创建逻辑错误：直接调用.FormatConditions(Selection.FormatConditions.Count)，但如果目标单元格还没设置过条件格式，这个索引根本不存在，直接报错。
• 需求逻辑缺失：只写了C列值小于8000的处理代码，大于8000的红色高亮完全没实现。

修复后的代码 #

方案1：直接设置单元格格式（适合一次性批量处理） #

这种方式不用条件格式，直接根据判断结果设置填充色，逻辑简单不容易出错：

Sub HighlightTotalCells()
    Dim i As Long
    Dim LastRow As Long
    Dim wS As Worksheet

    Set wS = ThisWorkbook.Sheets("Gateway")
    LastRow = LastRow_1(wS)
    
    ' 清除之前的格式残留
    wS.Range("C1:C" & LastRow).Interior.ColorIndex = xlNone
    
    For i = 1 To LastRow
        With wS
            If .Cells(i, 2).Value = "TOTAL" Then
                Select Case .Cells(i, 3).Value
                    Case Is < 8000
                        .Cells(i, 3).Interior.Color = RGB(0, 255, 0) ' 绿色高亮
                    Case Is > 8000
                        .Cells(i, 3).Interior.Color = RGB(255, 0, 0) ' 红色高亮
                    ' 等于8000的情况可按需添加处理逻辑
                End Select
            End If
        End With
    Next i
End Sub

Public Function LastRow_1(wS As Worksheet) As Long
    With wS
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            LastRow_1 = .Cells.Find(What:="*", _
                                After:=.Range("A1"), _
                                Lookat:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Row
        Else
            LastRow_1 = 1
        End If
    End With
End Function

方案2：正确使用条件格式（适合值变化后自动更新高亮） #

如果需要单元格数值改动时自动切换高亮颜色，用条件格式但要正确创建规则：

Sub AddTotalConditionalFormatting()
    Dim wS As Worksheet
    Dim targetRange As Range
    Dim cfLess As FormatCondition, cfGreater As FormatCondition
    
    Set wS = ThisWorkbook.Sheets("Gateway")
    Set targetRange = wS.Range("C1:C" & LastRow_1(wS))
    
    ' 清除旧的条件格式，避免重复叠加
    targetRange.FormatConditions.Delete
    
    ' 添加B列为TOTAL且C列小于8000的条件
    Set cfLess = targetRange.FormatConditions.Add(Type:=xlExpression, Formula1:= _
        "=AND($B1=""TOTAL"", $C1<8000)")
    With cfLess.Interior
        .Color = RGB(0, 255, 0)
    End With
    
    ' 添加B列为TOTAL且C列大于8000的条件
    Set cfGreater = targetRange.FormatConditions.Add(Type:=xlExpression, Formula1:= _
        "=AND($B1=""TOTAL"", $C1>8000)")
    With cfGreater.Interior
        .Color = RGB(255, 0, 0)
    End With
End Function

Public Function LastRow_1(wS As Worksheet) As Long
    With wS
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            LastRow_1 = .Cells.Find(What:="*", _
                                After:=.Range("A1"), _
                                Lookat:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Row
        Else
            LastRow_1 = 1
        End If
    End With
End Function

额外优化点 #

• 把LastRow_1的返回类型从Double改成Long，行号是整数类型，更合理。
• 修改Find的起始位置为A1，避免原代码中C1为空导致查找不到最后一行的问题。
• 增加清除旧格式/条件格式的步骤，防止重复执行代码时格式混乱。

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