Excel VBA：重叠区域条件格式异常及FormatConditions索引失效问题

我在Excel工作表中设置了多组条件格式，但用宏更新数据时，格式应用区域会变化、规则不断重复，彻底乱套。于是我改成在数据更新代码末尾用VBA重新设置条件格式，但碰到了重叠区域格式混乱的问题。

更新说明 #

@artodoro建议尝试修改ConditionalFormatting的索引编号，我自己试的时候没用，但粘贴他的代码后生效了。现在代码已经更新，但rng6的设置还是有问题，只能后续调整应用范围。想搞懂为什么用.FormatConditions(3)无法正确设置——给rng6设置这个索引时，格式没应用到目标区域，反而跑到rng2的格式上了。

更新后的完整代码 #

Sub Re_Audit()

Dim wsSmy As Worksheet
Dim LastRow As Integer, DeleteRow As Integer, LastColumn As Integer
Dim StartCells As Range
Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range, rng5 As Range
Set wsSmy = Workbooks("HRDA Audit_Master.xlsb").Sheets("SUMMARY")
Set StartCell = wsSmy.Range("A1")
LastRow = wsSmy.Cells(wsSmy.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = wsSmy.Cells(StartCell.Row, wsSmy.Columns.Count).End(xlToLeft).Column
wsSmy.Activate

Call DeleteConditionalFormatting

Set rng4 = wsSmy.Range("$N:$N")
With rng4
    .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="=$Q1=""Age Under 18"""
    .FormatConditions(1).Interior.Color = 15773696
    .FormatConditions(1).StopIfTrue = False
End With

Set rng5 = wsSmy.Range("$E:$H")
With rng5
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=OR('Mismatch Finder'!$B1=""Org vs Work Country Mismatch; ""','Mismatch Finder'!$C1=""Org Name vs Work Location & Country Mismatch; """)"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.Color = -16776961
.FormatConditions(1).StopIfTrue = False
End With

Set rng3 = wsSmy.Range("=$I:$J,$L:$L")
With rng3
    .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="='Mismatch Finder'!$G1=""Salary Band, Pay Basis/Grade Mismatch; """
    .FormatConditions(1).Font.Bold = True
    .FormatConditions(1).Font.Color = -709715
    .FormatConditions(1).StopIfTrue = False
End With

Set rng1 = wsSmy.Range("$O:$P")
With rng1
    .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="=$Q1=""Working Hours Discrepancy"""
    .FormatConditions(1).Interior.Color = 49407
    .FormatConditions(1).StopIfTrue = False
End With

Set rng2 = wsSmy.Range("$I:$K")
With rng2
    .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="='Mismatch Finder'!$F1=""Pay Basis/Grade vs Annualization Mismatch; """
    .FormatConditions(2).Font.Bold = True
    .FormatConditions(2).Borders.LineStyle = xlDashDot
    .FormatConditions(2).Borders.Color = -16777024
    .FormatConditions(2).Borders.Weight = xlThin
    .FormatConditions(2).Borders.Weight = xlThin
    .FormatConditions(2).StopIfTrue = False
End With

Set rng6 = wsSmy.Range("B:D")
With rng6
    .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="='Mismatch Finder'!$D1=""Org Name vs Pay Basis/Grade Mismatch; """
    .FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent1
    .FormatConditions(1).Interior.TintAndShade = 0.599963377788629
    .FormatConditions(1).StopIfTrue = False
End With

rng6.FormatConditions(1).ModifyAppliesToRange Range("$H:$J")

End Sub

出错的rng6代码片段 #

Set rng6 = wsSmy.Range("$H:$J") 'or ("H:J")
With rng6
    .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="='Mismatch Finder'!$D1=""Org Name vs Pay Basis/Grade Mismatch; """
    .FormatConditions(3).Interior.ThemeColor = xlThemeColorAccent1
    .FormatConditions(3).Interior.TintAndShade = 0.599963377788629
    .FormatConditions(3).StopIfTrue = False
End With

问题原因与解决方法 #

核心误解：.FormatConditions(index)是工作表全局索引，而非区域局部索引 #

你误以为这个索引是针对单个Range对象的局部编号，但实际上它是整个工作表所有条件格式的全局顺序计数。比如：

1. 给rng4添加规则后，全局索引为1
2. 给rng5添加规则后，全局索引为2
3. 给rng3添加规则后，全局索引为3
4. 到rng6时用.FormatConditions(3)，实际修改的是rng3对应的全局第3条规则，而不是刚给rng6添加的新规则——因为rng2和rng3有重叠区域$I:$J，所以看起来格式跑到了rng2上。

正确写法：引用刚添加的规则 #

有两种可靠方式：

1. 用.FormatConditions.Count引用最新添加的规则

With rng6
    .FormatConditions.Add Type:=xlExpression, Formula1:="='Mismatch Finder'!$D1=""Org Name vs Pay Basis/Grade Mismatch; """
    With .FormatConditions(.FormatConditions.Count)
        .Interior.ThemeColor = xlThemeColorAccent1
        .Interior.TintAndShade = 0.599963377788629
        .StopIfTrue = False
    End With
End With

2. 将新规则赋值给变量，直接操作变量

Dim cfRule As FormatCondition
Set rng6 = wsSmy.Range("$H:$J")
Set cfRule = rng6.FormatConditions.Add(Type:=xlExpression, Formula1:="='Mismatch Finder'!$D1=""Org Name vs Pay Basis/Grade Mismatch; """
With cfRule
    .Interior.ThemeColor = xlThemeColorAccent1
    .Interior.TintAndShade = 0.599963377788629
    .StopIfTrue = False
End With

额外注意 #

确保DeleteConditionalFormatting子过程确实清空了目标工作表的所有条件格式，避免旧规则干扰全局索引：

Sub DeleteConditionalFormatting()
    Workbooks("HRDA Audit_Master.xlsb").Sheets("SUMMARY").Cells.FormatConditions.Delete
End Sub

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