How to use VBA for conditional formatting on the same range

Question

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.

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.

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?

Answer 1

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

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

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.

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:

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

Answer 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.

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

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.

Answer 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.

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.

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. :-)