Conditional Formatting within a Table

JBF_54 

VBA can be used to set, reset or keep rules in order. Tricky? Yes. But this works for me to consolidate.

This is brute force. But that is because I am not so clever.

Sub has 2 arguments: oTable (the table object) and colName (string) that you want to consolidate.

Sub ConsolidateConditionalFormatting(oTable, colName)
' used to keep conditional formatting from fragmenting
' survey all rows in column
' all rows in a column should end up with the same conditionals
' how and where to find "the" correct set of conditionals? ...normally it will be 1 or 2 conditionals per column...
' make all rows have the same conditionals as whichever row has the most conditionals
' Problem? yes. this is equalizing by maxCount not by conditional contents. But who cares.
' example. Row 3 may have have conditionals a, b, c. row 1000 may have a, b, d, e. all rows will end up with a, b, d, e
' is that what you want?

If oTable.ListColumns(colName).Index = 0 Then
Exit Sub ' col does not exist. might want to do error trapping.
End If

Dim ColIndex As Integer
ColIndex = oTable.ListColumns(colName).Index

' if all rows have 0 conditional formats, exit
' find the row with the most conditional formats use it to populates all other rows
' ? what if 2 rows have say 2 conditionals but they are different? oops.
Dim i, rowWithMax, iCount As Long
rowWithMax = 0
iCount = 0
For i = 1 To oTable.ListRows.count
If oTable.DataBodyRange(i, ColIndex).FormatConditions.count > iCount Then
rowWithMax = i
iCount = oTable.DataBodyRange(i, ColIndex).FormatConditions.count
End If
Next i

If iCount = 0 Then
Exit Sub ' no format conditions in any rows
End If

Dim FCO As FormatCondition
For i = 1 To oTable.DataBodyRange(rowWithMax, ColIndex).FormatConditions.count
Set FCO = oTable.DataBodyRange(rowWithMax, ColIndex).FormatConditions(i)
FCO.ModifyAppliesToRange Range(oTable.name & "[" & colName & "]")
Next

End Sub ' ConsolidateConditionalFormatting(oTable, colName)