如何在同一范围内使用VBA进行条件格式化

I am trying to use VBA to do conditional formatting on a similar range. I am sure that the error in my code has something to do with the precedence but I cannot figure out what it is. I am trying to format essentially the same group of cells. If column CI contains the text "TIES MATERIAL" then it should format the cells, in columns CU:DD in the case below, to the color white for that specific row. If that column does not contain the text string and the value has changed from its original value the cell should be changed to the color red.

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

Here is the code I have for making it white:

以下是我将其设为白色的代码:

 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

Here is the code I have for making it red:

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

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

Here is what the conditional format looks like when I call the white_format then call the red_format in the same sub-routine.

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

The formulas show up correctly but the colors are in the opposite sections that they need to be in. What am I doing wrong? I also know that my code is not the most efficient that it could/should be. How else could I re-write it?

公式显示正确,但颜色在他们需要的相反部分。我做错了什么?我也知道我的代码不是最有效的。我怎么能重写呢?

3 个解决方案

#1

Formula1:="=$CI3=""TIES MATERIAL"""
"If column CI contains the text "TIES MATERIAL" then it should format the cell to the color white"

“如果列CI包含文本”TIES MATERIAL“,那么它应该将单元格格式化为白色”

To do this, your format condition should be:

为此,您的格式条件应为:

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

As for the second condition I still dont get the idea you are trying to achieve. However the formula might be correct, but the problem is that you are referring to it wrongly:

至于第二个条件,我仍然没有得到你想要实现的想法。但是公式可能是正确的,但问题是你错误地提到它:

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

As this is the second FormatCondition, you should refer to it as index (2). This explains why you were actually overwriting the format of the first condition with red, while the second had no format set.

由于这是第二个FormatCondition,您应该将其称为index(2)。这解释了为什么你实际上用红色覆盖第一个条件的格式,而第二个条件没有格式化。

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

(This supposes both your CFs apply on the same range). If not, a usually safe method is to get a reference on the CF directly and work with it:

(这假设您的CF都适用于相同的范围)。如果没有,通常安全的方法是直接在CF上获得参考并使用它:

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

#2

_{The formulas show up correctly but the colors are in the opposite sections that they need to be in.}

公式显示正确,但颜色在他们需要的相反部分。

That is not entirely correct. You create one CFR with a white background and set it to the top of the list. Then you create a second but before putting it to the top of the list, you change the top of the list CFR to a red background. So you have one CFR that used to be a white background and is now red background and a second CFR with no background.

这不完全正确。您创建一个白色背景的CFR并将其设置为列表的顶部。然后创建第二个但在将其放到列表顶部之前,将列表CFR的顶部更改为红色背景。所以你有一个曾经是白色背景的CFR,现在是红色背景,第二个CFR没有背景。

I'm going to assume that the formula for the red CFR is correct. Someone else suggested a non-volatile change.

我将假设红色CFR的公式是正确的。其他人提出了非易变的变化。

Option Explicit

Private Sub white_red_CFRs()
    'This section adds the formatting condition of white cells to the cells that are changed by the PEMCON
    Dim lRow As Long, lCol As Long, firstCell As String, lastCell As String
    Dim colCount  As Long, lastHeaderCell  As Long

    With ActiveWorkbook.Worksheets("Material")
        lRow = .Range("A2").End(xlDown).Row
        lCol = .Range("A2").End(xlToRight).Column
        firstCell = .Range("CU3").Address(False, False)
        lastCell = .Cells(lRow, lCol).Address(False, False)

        With .Range(firstCell, lastCell)
            .FormatConditions.Delete
            With .FormatConditions.Add(Type:=xlExpression, Formula1:="=$CI3=""TIES MATERIAL""")
                .Interior.Color = 16777215 'this is the color white
                .SetFirstPriority
                .StopIfTrue = True
            End With

            With .FormatConditions.Add(Type:=xlExpression, Formula1:="=OFFSET($A$1,ROW()-1,COLUMN()-1)<>OFFSET($A$1,ROW()-1,COLUMN()+" & colCount & ")")
                .Interior.Color = 255 'this is the color red
                .StopIfTrue = True
            End With
        End With
    End With

End Sub

When you record a CFR, teh part of Excel that translates your actions to code does not know how many CFRs there already are so it makes each the first one so it can continue with configuration and refer to the new CFR as .FormatConditions(1). You were setting the format configuration to .FormatConditions(1) before setting the second CFR as the top (1) CFR. I prefer to use the With .Add method instead.

当您记录CFR时,将您的操作转换为代码的Excel的一部分不知道已经存在多少CFR,因此它使每个CFR成为第一个,因此它可以继续配置并将新CFR称为.FormatConditions(1) 。在将第二个CFR设置为top(1)CFR之前,您将格式配置设置为.FormatConditions(1)。我更喜欢使用With .Add方法。

#3

OK. Again it is a bit difficult to understand what you tried to achieve and what went wrong as you gave no test material (and the columns are that far to the right that I had to make it simpler...), but it may be just me.

好。同样有点难以理解你试图实现什么以及出了什么问题,因为你没有给出任何测试材料(并且这些列是非常正确的,我必须使它变得更简单......),但它可能只是我。

To format the cell color to white when the column CI read TIES MATERIAL your rule formula "=$CI3=""TIES MATERIAL""" works fine but I just wonder what colour would they be when they are not white? Would they be red due to the other rule? Then they are in wrong order as conflicting rules will take the rule higher in the list to be applied. The 'Stop if true' is for the pre 2007 versions of Excel.

当列CI读取时,将单元格颜色格式化为白色TIES MATERIAL您的规则公式“= $ CI3 =”“TIES MATERIAL”“”工作正常,但我只是想知道当它们不是白色时它们会是什么颜色?由于另一条规则,它们会变红吗?然后它们的顺序错误,因为冲突的规则会使列表中的规则更高。 “如果为真,则停止”适用于2007年之前的Excel版本。

AND one can see an error in the image and it comes from your code: In the Manage Rules dialog image you see that the 'white rule' is marked as 'No format set'. That is because you have referred to the FormatConditions(1) in the both of the procedures. If you have first run the 'white rule' and the 'red' the latter has been set OK but simultaneously broken the first (number one) most probably as the references to the range don't match.

并且可以看到图像中的错误,它来自您的代码:在“管理规则”对话框图像中,您会看到“白色规则”被标记为“无格式设置”。那是因为您在两个过程中都引用了FormatConditions(1)。如果您首先运行'白色规则'和'红色',后者已设置好但同时打破第一个(第一个),最可能是因为对范围的引用不匹配。

So maybe you want to run the 'white rule' first and refer to FormatConditions(2) when creating the 'red', but as I said hard to tell. :-)

所以也许你想先创建'白色规则'并在创建'红色'时参考FormatConditions(2),但正如我所说的那样。 :-)

#1