Excel VBA多列条件格式异常：P/Q/R列未生效求助

问题根源 #

你的代码里，cond变量会被每次执行的Set cond = [列].FormatConditions.Add(...)覆盖，最终只有最后一次赋值的S列的条件格式被应用了With cond中的样式设置。O列看似生效，大概率是之前手动操作或旧代码残留的设置，并非当前代码的作用。

解决方案1：遍历列批量设置（高效简洁） #

通过循环遍历目标列，每添加一个条件格式就立即设置对应的样式：

Function ConditionalFormatOneNo()
    Dim targetCol As Range
    Dim currentCond As FormatCondition
    
    ' 遍历需要设置的列
    For Each targetCol In Array(Range("O:O"), Range("P:P"), Range("Q:Q"), Range("R:R"), Range("S:S"))
        ' 为当前列添加条件格式规则
        Set currentCond = targetCol.FormatConditions.Add(xlCellValue, xlEqual, "NO")
        ' 设置格式样式
        With currentCond
            .Interior.Color = vbRed
            .Font.Color = vbBlack
            .Font.Bold = True
        End With
    Next targetCol
End Function

解决方案2：独立变量+复用子过程（适合多规则场景） #

如果需要保留各列的独立变量，或者要给YES、EMPTY等规则复用格式逻辑，可以抽离通用设置过程：

Function ConditionalFormatOneNo()
    Dim rgO As Range, rgP As Range, rgQ As Range, rgR As Range, rgS As Range
    Dim condO As FormatCondition, condP As FormatCondition, condQ As FormatCondition
    Dim condR As FormatCondition, condS As FormatCondition
    
    Set rgO = Range("O:O")
    Set rgP = Range("P:P")
    Set rgQ = Range("Q:Q")
    Set rgR = Range("R:R")
    Set rgS = Range("S:S")
    
    ' 为每列添加条件规则并保存对应对象
    Set condO = rgO.FormatConditions.Add(xlCellValue, xlEqual, "NO")
    Set condP = rgP.FormatConditions.Add(xlCellValue, xlEqual, "NO")
    Set condQ = rgQ.FormatConditions.Add(xlCellValue, xlEqual, "NO")
    Set condR = rgR.FormatConditions.Add(xlCellValue, xlEqual, "NO")
    Set condS = rgS.FormatConditions.Add(xlCellValue, xlEqual, "NO")
    
    ' 调用通用过程设置格式
    ApplyCondFormat condO
    ApplyCondFormat condP
    ApplyCondFormat condQ
    ApplyCondFormat condR
    ApplyCondFormat condS
End Function

' 通用格式设置子过程，YES、EMPTY的函数也可以调用这个
Sub ApplyCondFormat(cond As FormatCondition)
    With cond
        .Interior.Color = vbRed
        .Font.Color = vbBlack
        .Font.Bold = True
    End With
End Sub

扩展优化（针对YES/EMPTY规则） #

如果要给YES、EMPTY规则设置不同样式，可以修改通用过程，把样式参数传进去：

Sub ApplyCondFormat(cond As FormatCondition, fillColor As Long, fontColor As Long, isBold As Boolean)
    With cond
        .Interior.Color = fillColor
        .Font.Color = fontColor
        .Font.Bold = isBold
    End With
End Sub

调用时只需传入对应参数，比如YES规则可以用：
ApplyCondFormat condYes, vbGreen, vbWhite, True

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