vba - 如何使用VBA对同一范围进行条件格式化

Question

我正在尝试使用 VBA 在类似的范围内进行条件格式化。我确信我的代码中的错误与优先级有关，但我无法弄清楚它是什么。我正在尝试格式化本质上相同的单元格组。如果列 CI 包含文本“TIES MATERIAL”，则应将下例中的 CU:DD 列中的单元格格式设置为该特定行的白色。如果该列不包含文本字符串并且值已从其原始值更改为单元格应更改为红色。

这是我将其变为白色的代码:

 Private Sub white_format()
    'This section adds the formatting condition of white cells to the cells that are changed by the PEMCON

    ActiveWorkbook.Sheets("Material").Activate

    Dim lRow As Integer
    Dim lCol As Integer

    lRow = ActiveWorkbook.Sheets("Material").Range("A2").End(xlDown).Row
    lCol = ActiveWorkbook.Sheets("Material").Range("A2").End(xlToRight).Column

    firstCell = ActiveWorkbook.Sheets("Material").Range("CU3").Address(False, False)
    lastCell = ActiveWorkbook.Sheets("Material").Cells(lRow, lCol).Address(False, False)

    Debug.Print "firstCell: " & firstCell
    Debug.Print "lastHeaderCell: " & lastHeaderCell
    Debug.Print "colCount: " & colCount

    'Defines the array of the CU3 to the last used cell and it checks to see if the coorisponding cell in column CI has TIES MATERIAL in it
        ActiveWorkbook.Sheets("Material").Range(firstCell, lastCell).FormatConditions.Add Type:=xlExpression, Formula1:="=$CI3=""TIES MATERIAL"""
        ActiveWorkbook.Sheets("Material").Range(firstCell, lastCell).FormatConditions(1).SetFirstPriority

        With ActiveWorkbook.Sheets("Material").Range(firstCell, lastCell).FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 16777215 'this is the color white
            .TintAndShade = 0
        End With
        ActiveWorkbook.Sheets("Material").Range(firstCell, lastCell).FormatConditions(1).StopIfTrue = True


    End Sub

这是我将其设为红色的代码:

Private Sub Red_Format()

Dim lRow As Integer
Dim lCol As Integer

lRow = ActiveWorkbook.Sheets("Material").Range("A2").End(xlDown).Row
lCol = ActiveWorkbook.Sheets("Material").Range("A2").End(xlToRight).Column

firstCell = ActiveWorkbook.Sheets("Material").Range("CU2").Address(False, False)
lastCell = ActiveWorkbook.Sheets("Material").Cells(lRow, lCol).Address(False, False)
formatRange = ActiveWorkbook.Sheets("Material").Range(firstCell, lastCell)

lastHeaderCell = ActiveWorkbook.Sheets("Material").Cells(2, lCol).Address(False, False)
colCount = ActiveWorkbook.Sheets("Material").Range(firstCell, lastHeaderCell).Columns.Count

'Defines the array of the CU2 to the last used cell and adds the formatting to turn it red if it has been altered.
    ActiveWorkbook.Sheets("Material").Range(firstCell, lastCell).FormatConditions.Add Type:=xlExpression, Formula1:="=OFFSET($A$1,ROW()-1,COLUMN()-1)<>OFFSET($A$1,ROW()-1,COLUMN()+" & colCount & ")"
    'ActiveWorkbook.Sheets("Material").Range(firstCell, lastCell).FormatConditions(1).SetFirstPriority

    With ActiveWorkbook.Sheets("Material").Range(firstCell, lastCell).FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    ActiveWorkbook.Sheets("Material").Range(firstCell, lastCell).FormatConditions(1).StopIfTrue = False


End Sub

以下是当我在同一子例程中调用 white_format 然后调用 red_format 时的条件格式。

公式显示正确，但颜色位于它们需要的相反部分。我做错了什么？我也知道我的代码并不是最有效的。我还能怎样重写它？

Answer 1

Formula1:="=$CI3=""TIES MATERIAL"""

"If column CI contains the text "TIES MATERIAL" then it should format the cell to the color white"

为此，您的格式条件应该是:

Formula1:="=ISNUMBER(SEARCH(""TIES MATERIAL"", $CI" & firstCell.Row & "))"

至于第二个条件，我仍然不明白你想要实现的想法。然而，该公式可能是正确的，但问题是您错误地引用了它:

With ActiveWorkbook.Sheets("Material").Range(firstCell, lastCell).FormatConditions(1).Interior

由于这是第二个 FormatCondition，因此您应该将其称为索引(2)。这解释了为什么您实际上用红色覆盖第一个条件的格式，而第二个条件没有设置格式。

With ActiveWorkbook.Sheets("Material").Range(firstCell, lastCell).FormatConditions(2).Interior
'                                                                                 ^^^

(假设您的两个 CF 适用于同一范围)。如果没有，通常安全的方法是直接获取 CF 上的引用并使用它:

With myRange.formatConditions.Add(xlExpression, formula1)
  .Interior.ColorIndex = ...
  . etc...
End With