如何编写VBA For循环为Excel多区域批量应用条件格式并调整引用

修正后可直接运行的VBA代码 #

Sub 批量添加条件格式()
    Dim yearStartRows ' 存储三个年份块的起始行
    Dim i As Integer, o As Integer
    Dim applyRng As Range, judgeCol As Integer, formulaStr As String
    Const BLOCK_ROW_COUNT = 31 ' 每个年份块的行数，从起始行往下共31行，可根据实际调整
    
    ' 初始化三个年份的起始行，对应你提到的C6、C45、C84
    yearStartRows = Array(6, 45, 84)
    
    ' 外层循环遍历3个年份块
    For o = LBound(yearStartRows) To UBound(yearStartRows)
        ' 内层循环遍历12组列
        For i = 1 To 12
            ' 计算当前组的判断列：第1组D(4)、第2组G(7)、第3组J(10)，每组间隔3列
            judgeCol = 3 * i + 1
            ' 确定当前要应用格式的3列范围
            Set applyRng = Range(Cells(yearStartRows(o), 3 * i), Cells(yearStartRows(o) + BLOCK_ROW_COUNT - 1, 3 * i + 2))
            
            ' 自动拼接条件格式公式，生成$D6、$G6、$D84这类符合要求的引用
            formulaStr = "=" & Columns(judgeCol).Address(RowAbsolute:=False, ColumnAbsolute:=True) & yearStartRows(o) & "=""Sun"""
            
            ' 直接操作区域应用格式，避免Select带来的运行不稳定问题
            With applyRng.FormatConditions.Add(Type:=xlExpression, Formula1:=formulaStr)
                .SetFirstPriority
                With .Interior
                    .Pattern = xlLightVertical
                    .PatternColor = 65535
                    .ColorIndex = xlAutomatic
                    .PatternTintAndShade = 0
                End With
            End With
        Next i
    Next o
End Sub

关键调整说明 #

• 预定义三个年份块的起始行数组，无需手动计算偏移，避免行号匹配错误
• 按每组3列的规则自动计算判断列编号，通过Address方法直接生成$D、$G这类绝对列标识，拼接对应起始行后完全匹配你需要的引用规则
• 去掉了Select操作，直接对目标范围操作，避免窗口闪烁和选中位置偏差导致的错误
• 修正了原有代码的语法问题：条件格式参数缺逗号、拼写错误、范围未关联年份块等

注意事项 #

• 如果需要先清理目标区域原有条件格式，可以在应用新格式前加一行applyRng.FormatConditions.Delete
• 如果后续年份块的行数有调整，直接修改BLOCK_ROW_COUNT常量的数值即可

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