如何在VBA条件格式中排除空白单元格避免颜色规则失效？

修改条件格式的公式以排除B列空白单元格，同时优化数据区域的选取逻辑，确保格式准确应用到所有有效行。

修改后的VBA代码：

Sub Alternate_Column_Color()
    Dim r As Range
    ' 基于B列最后一行非空数据，选取A2到M列的有效区域
    Set r = Range("A2:M" & Cells(Rows.Count, "B").End(xlUp).Row)
    
    ' 清除已有条件格式，避免重复叠加
    r.FormatConditions.Delete
    
    ' 添加排除空白的条件格式公式
    r.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ISODD(SUMPRODUCT(--(MATCH($B$1:$B2,$B$1:$B2,0)=ROW($B$1:$B2)-ROW($B$1)+1),--($B$1:$B2<>"""")))"
    
    r.FormatConditions(r.FormatConditions.Count).SetFirstPriority
    With r.FormatConditions(1)
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.ColorIndex = 19
        .Font.ColorIndex = 26
    End With
    r.FormatConditions(1).StopIfTrue = False
    
    Set r = Nothing
End Sub

关键修改说明 #

• 数据区域优化：
  原代码用Range("A2:M2").End(xlDown)可能在中间有空白行时提前终止，改为基于B列最后一行非空行确定范围，确保覆盖所有有效数据行。

• 条件格式公式调整：
  原公式会将空白单元格视为独立分组，导致后续交替颜色混乱。新公式通过以下逻辑排除空白：

  1. --($B$1:$B2<>"""")：将非空白单元格转为数值1，空白转为0，仅统计非空白内容
  2. MATCH($B$1:$B2,$B$1:$B2,0)=ROW($B$1:$B2)-ROW($B$1)+1：判断当前单元格是否是对应值的首次出现，确保仅统计唯一值的数量
  3. 用SUMPRODUCT求和得到当前行之前的非空白唯一值总数，再通过ISODD判断奇偶来触发颜色格式

修改后，即使清除B列单元格内容变为空白，也不会影响其他行的交替颜色格式，空白单元格不会被计入分组计数。

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