VBA how do I loop through each table in an array

Question

I want to clear and reset the conditional formatting on certain ListObjects in my work book. How do I create an array so that I can use for loops to apply the formatting. This was originally written to apply to the whole sheet, but now I want to restrict the routine to a table.

Here is my code:

Sub Reset()


'Dim ws As Worksheet
Dim lo As Variant
Dim ArrTab As Variant
Dim ws As Variant
Dim Item As Variant
Dim ArrGreen As Variant

ArrTab = Array("Dept1710", "Dept1711", "Dept1713", "Dept1715", "Dept1716", "Dept1717")
ArrGreen = Worksheets("Drop down").Range("Q14:Q18").Value


For Each lo In ArrTab


lo.Select
Selection.FormatConditions.Delete

    For Each Item In ArrGreen
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:=Item
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Next Item

Next lo

End Sub

ArrTab has all of the table names.

Storax · Answer 1 · 2022-07-24T17:56:14.183

If the tables resp. listobject all on the same worksheet the following fix could do it

Sub Reset()

    'Dim ws As Worksheet
    Dim lo As ListObject
    Dim ArrTab As Variant
    Dim ws As Variant
    Dim Item As Variant
    Dim ArrGreen As Variant

    ArrTab = Array("Dept1710", "Dept1711", "Dept1713", "Dept1715", "Dept1716", "Dept1717")
    ArrGreen = Worksheets("Drop down").Range("Q14:Q18").Value

    Dim sngElement As Variant                         ' Variable to loop throgh the array
    For Each sngElement In ArrTab

        Set lo = ActiveSheet.ListObjects(sngElement)  ' Assign the listobject
        ' lo.Range.Select                             ' uncomment if you need the complete listobject
        lo.DataBodyRange.Select                       ' select the data (without headers)
        Selection.FormatConditions.Delete

        For Each Item In ArrGreen
            Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:=Item
            Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
            With Selection.FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 5287936
                .TintAndShade = 0
            End With
        Next Item

    Next sngElement

End Sub

But normally you do not need Select

VBA how do I loop through each table in an array

1 Answers1