VBA 格式条件跳过第一行

Question

我试图通过对每列应用不同的条件格式来突出显示值。但是，我找不到跳过整个第一行的有效方法，因为它本质上是标题行，不应应用突出显示。以下是我为某些列准备的代码：

'highlight first row white
With y.Sheets(Sh).Range("Y1:Y1").FormatConditions.Add(Type:=xlExpression, Formula1:="=NOT(ISBLANK(Y1))")
.Interior.Color = rgbWhite
With y.Sheets(Sh).Range("Y:Y").FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="Z")
   .Interior.Color = rgbWhite
    End With
With y.Sheets(Sh).Range("Y:Y").FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="NA")
   .Interior.Color = rgbWhite
    End With
With y.Sheets(Sh).Range("Y:Y").FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=" & imput)
   .Interior.Color = rgbOrange
    End With
   End With
'highlight first row white
With y.Sheets(Sh).Range("Z1:Z1").FormatConditions.Add(Type:=xlExpression, Formula1:="=NOT(ISBLANK(Z1))")
.Interior.Color = rgbWhite
   End With
With y.Sheets(Sh).Range("Z:Z").FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="Z")
   .Interior.Color = rgbWhite
    End With
With y.Sheets(Sh).Range("Z:Z").FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="NA")
   .Interior.Color = rgbWhite
    End With
With y.Sheets(Sh).Range("Z:Z").FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=" & imput)
   .Interior.Color = RGB(255, 153, 0) 'Orange
    End With

我几乎必须将第一with语句(第一行颜色为白色(添加到每一列，以覆盖后面的格式。有没有更有效的方法让第一行保持未填充？

Answer 1

您无需将其他单元格着色为白色，可以将 Y 列和 Z 组合在一起;像这样的东西...

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
With y.Sheets(Sh).Range("Y2:Z" & LastRow).FormatConditions.Add(Type:=xlExpression, Formula1:="=" & imput)
    .Interior.Color = RGB(255, 153, 0) 'Orange
End With

Answer 2

使用此代码，可以更轻松地定义和设置所需的所有 CF 规则(适用于所有列(。它将从第 2 行(波纹管标题(开始的所有规则应用到最后使用的行，因此标题不受影响

要从工作表中删除所有当前规则并开始清理，请取消注释此行

• ws.Columns.FormatConditions.Delete

---

Option Explicit
Public Sub CFRules()
    Dim ws As Worksheet, minVal As String, lr As Long
    Dim rngCol1Rows As Range, rngCol2Rows As Range
    Set ws = ActiveSheet
    lr = ws.UsedRange.Rows.Count
    minVal = 3  '(imput)
    'ws.Columns.FormatConditions.Delete  'If uncommented, this will remove all CF rules!
    Set rngCol1Rows = ws.Range("Y2:Y" & lr) 'Col Y, except Header (Start Range at Row 2)
    Set rngCol2Rows = ws.Range("Z2:Z" & lr) 'Col Z, except Header
    SetCFRule rngCol1Rows, "=AND(Y2>=" & minVal & ", ISNUMBER(Y2))", rgbOrange
    SetCFRule rngCol2Rows, "=AND(Z2>=" & minVal & ", ISNUMBER(Z2))", RGB(255, 153, 0)
    SetCFRule rngCol1Rows, "=OR(Y2=""Z"", Y2=""NA"")", vbWhite
    SetCFRule rngCol2Rows, "=OR(Z2=""Z"", Z2=""NA"")", vbWhite
End Sub
Private Sub SetCFRule(ByRef cfRng As Range, cfFormula As String, ByVal cfColor As Long)
    With cfRng
        .FormatConditions.Add Type:=xlExpression, Formula1:=cfFormula
        .FormatConditions(cfRng.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1)
            .Interior.Color = cfColor
            .StopIfTrue = False
        End With
    End With
End Sub