VBA如何实现按列平均值循环设置单元格条件格式标色

安全阿岳

2026-8-30

问题场景

待处理数据区域为D5:AM39
每列的平均值已存储在对应列的第40行单元格：D列平均值在D40、E列在E40，按顺序延伸至AM列的AM40
目标条件格式规则：单元格数值高于所在列平均值时填充绿色，低于所在列平均值时填充红色

初始代码错误排查

用户最初编写的VBA脚本无法正常运行，初始代码如下：

Application.CutCopyMode = False

With Range(Cells(5, 39), Cells(4, 39))
  .FormatConditions.Delete

  .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    Formula1:="=$D40"
 .FormatConditions(1).Interior.color = RGB(0, 150, 0)

End With
End Sub

代码存在2个核心错误：

区域引用完全错误：Cells(行号, 列号)的参数顺序为先行后列，Range(Cells(5, 39), Cells(4, 39))实际选中的是AM列第4-5行共2个单元格，完全偏离目标数据区域；目标区域D列对应列号4、AM列对应列号39，数据行范围是5-39行，正确的区域引用应为Range(Cells(5, 4), Cells(39, 39))
条件公式引用错误：公式=$D40对列加了绝对引用符号$，会导致所有列的单元格都固定和D40的平均值对比，无法自动匹配当前列的平均值

录制宏版本问题说明

用户后续通过宏录制得到了接近需求的代码，但出现颜色错位（应标绿单元格标红、应标红单元格标绿）的问题，录制代码如下：

With Range(Cells(39, 4), Cells(5, 39)).Select
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    Formula1:="=D$40"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .color = -16752384
    .TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .color = 13561798
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
    Formula1:="=D$40"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .color = -16383844
    .TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .color = 13551615
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
  End With
  End Sub

该版本的核心问题：

存在冗余的Select操作：VBA操作单元格区域无需先选中对象，该写法不仅效率低，还容易因当前选中对象变化触发错误
规则优先级逻辑混乱：每新增一条条件格式就调用SetFirstPriority将其设为最高优先级，后添加的「小于平均值」规则会覆盖先加的「大于平均值」规则，触发顺序颠倒导致颜色错位
语法错误：With Range(Cells(39, 4), Cells(5, 39)).Select的写法不符合With块语法规范

修正后可用代码

Sub SetColumnAvgColor()
    Application.CutCopyMode = False
    ' 定义目标数据区域
    Dim dataRng As Range
    Set dataRng = Range(Cells(5, 4), Cells(39, 39)) ' 对应D5:AM39区域
    
    With dataRng
        ' 先清空原有条件格式，避免旧规则干扰
        .FormatConditions.Delete
        
        ' 添加大于平均值规则：填充绿色
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=D$40" ' 仅锁定行号，列号为相对引用，自动匹配当前列第40行平均值
        With .FormatConditions(1)
            .Interior.Color = RGB(0, 150, 0)
            .Font.Color = vbWhite ' 可选：设置白色字体提升可读性
            .StopIfTrue = False
        End With
        
        ' 添加小于平均值规则：填充红色
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=D$40"
        With .FormatConditions(2)
            .Interior.Color = RGB(220, 0, 0)
            .Font.Color = vbWhite
            .StopIfTrue = False
        End With
    End With
End Sub

代码说明：

条件公式使用=D$40，仅锁定行号40，列号为相对引用，应用到其他列时会自动调整为对应列的40行单元格（如E列自动变为=E$40，F列变为=F$40），符合按列匹配平均值的需求
执行前先清空区域内原有条件格式，避免旧规则冲突
直接操作Range对象，无冗余Select操作，运行更稳定

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