VBA invalid procedure call or argument: Conditional formatting for items stored in an array

Question

I want to clear and reset the conditional formatting rules for my workbook. There are groups of values that I want conditionally formatted to show as Green, Yellow, Orange, Red, and Pink. The values for each color group are stored in an array.

Below is my code so far, but only showing the for loops for green and yellow. I get the error on this line Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual,Formula1:=item

Sub ResetFormat()

Dim ws As Worksheet
Dim item As Variant

Dim arrGreen As Variant
    arrGreen = Array(Worksheets("Drop down").Range("N11:N14"))
Dim arrYellow As Variant
    arrYellow = Array(Worksheets("Drop down").Range("O11:O13"))
Dim arrOrange As Variant
    arrOrange = Array(Worksheets("Drop down").Range("P11:P14"))
Dim arrRed As Variant
    arrRed = Array(Worksheets("Drop down").Range("Q11:Q14"))
Dim arrPink As Variant
    arrPink = Array(Worksheets("Drop down").Range("R11:R12"))
    
For Each ws In Sheets
Cells.Select
        Selection.Cells.FormatConditions.Delete
    For Each item In arrGreen
        
        
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
            Formula1:=item
        
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 5287936
            .TintAndShade = 0
        End With
    Next item
    
    For Each item In arrYellow
        
        
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
            Formula1:=item
        
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 49407
            .TintAndShade = 0
        End With
    Next item
    

Next ws

End Sub

`arrGreen` is an array with one element, the range N11:N14. `For Each item In arrGreen` enumerates the array elements, all one of them. Then you attempt to set format conditions for all cell of the active sheet to be "equal" to that entire range, which does not make sense. In doing so you are looping over all existing sheets, although you keep trying to set the format to the same (active) sheet. — GSerg, Jun 05 '22 at 18:26
I think you're using the wrong method to create your arrays. See: [Creating an Array from a Range in VBA](https://stackoverflow.com/questions/37689847/creating-an-array-from-a-range-in-vba). So, it should be `arrGreen = Worksheets("Drop down").Range("N11:N14").Value` etc. Not sure, incidentally, why you're not simply using ranges. `Dim rngGreen as Range` and `set rngGreen = Worksheets("Drop down").Range("N11:N14")` and further below use `For Each item In rngGreen` — ouroboros1, Jun 05 '22 at 18:31
What do you think about `arrGreen` array? What do you think it contains? Can you share this expectation, please? Do you understand what `Cells.Select` does? Supposing that working only on the active sheet can be solved, do you really want setting format conditions for **all cells in each sheet**? Please, edit your question and try explaining **IN WORDS** what you try accomplishing. — FaneDuru, Jun 05 '22 at 19:05
@ouroboros1 Thank you for the suggestion. Using a range worked. I've been told in the past to try and use arrays rather than ranges, but I don't know what criteria determines the best application of either. — Bert328, Jun 05 '22 at 20:45

score 1 · Answer 1 · answered Jun 05 '22 at 20:44

What worked for me was using a range instead of an array, and adding the following line:

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

The resulting code:

Sub ResetFormat()

Dim ws As Worksheet
Dim item As Variant

Dim rngGreen As Range
    Set rngGreen = Worksheets("Drop down").Range("N11:N14")
Dim arrYellow As Range
    Set rngYellow = Worksheets("Drop down").Range("O11:O13")
Dim rngOrange As Range
    Set rngOrange = Worksheets("Drop down").Range("P11:P14")
Dim rngRed As Range
    Set rngRed = Worksheets("Drop down").Range("Q11:Q14")
Dim rngPink As Range
    Set rngPink = Worksheets("Drop down").Range("R11:R12")

    
For Each ws In Sheets
    ws.Activate
    Cells.Select
    Selection.Cells.FormatConditions.Delete
    For Each item In rngGreen
        
        Cells.Select
        Selection.FormatConditions.Add Type:=xlTextString, String:=item, TextOperator:=xlEqual

        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .Color = 5287936
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    Next item
    
    For Each item In rngYellow
        
        Cells.Select
        Selection.FormatConditions.Add Type:=xlTextString, String:=item, TextOperator:=xlEqual
        
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .Color = 49407
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    Next item
    

Next ws

End Sub

VBA invalid procedure call or argument: Conditional formatting for items stored in an array

1 Answers1