Addressing several columns in a ListObject for conditional formatting

Question

I'm working with a table (Excel 365) which contains 50 columns and about 7000 rows (increasing). The table is formatted as ListObject. So far so good.
Now I need to address 8 columns - not next to each other and all at once - to assign 4 different conditional formats to them. Procedure

Set condition = rng.FormatConditions.Add(xlExpression, Formula1:="=.....")

Obviousely it is not possible to create another array out of the ListObject (Message: types incompatible). At least I couldn't find a way yet. I also tried

With rng 
    .FormatConditions.Add

..... but VBA always asks for a "=".

Anybody any ideas? Is it possible at all? Thanks for your help!

Answer 1

Your task is to set rng to the correct Range before applying the FormatConditions. The columns of a table are accessible with ListColumns. This is a collection of all columns of a table. Therefore you can address a single column either via index or via column name (the name of a column is defined by the header cell).

The range of a column can be accessed by the property Range. However, that includes the header row, and usually you don't want to apply the formatting to the header. Instead, you can use the property DataBodyRange, this omits the header cell.

To get all cells of all columns of interest, simply use the Union-command.

So your code could look like this:

Dim table As ListObject
Set table = ActiveSheet.ListObjects(1)  ' Change to your needs
Dim rng As Range

' Define the list of columns you want to use.     
Dim colList, i As Long
colList = Array(2, 3, 5)                     ' Either by index...
colList = Array("Name", "Street", "Info") ' ... or by name

' Loop over the columns and build the Range
For i = LBound(colList) To UBound(colList)
    Dim col As ListColumn
    Set col = table.ListColumns(colList(i))
    If rng Is Nothing Then
        Set rng = col.DataBodyRange
    Else
        Set rng = Union(rng, col.DataBodyRange)
    End If
Next

' Optional: Remove existing FormatConditions  
Do While table.DataBodyRange.FormatConditions.Count > 0
    table.DataBodyRange.FormatConditions(1).Delete
Loop

' Now set new FormatConditions for the Range
Dim fc As FormatCondition
Set fc = rng.FormatConditions.Add(xlExpression, Formula1:="=" & rng.Cells(1).Address(False, False) & ">5")
fc.Interior.Color = vbYellow

Note that once you have applied the conditional formatting to the table columns, it will "grow" when you add new rows - with other words the new rows will have the same formatting automatically.