print all conditional formatting rules for a spreadsheet

Question

I would like VBA code to print every condition formatting Rule within a spreadsheet, including Rule Type, Rule Description (formula), Colour and Cell Range that the rule applies to. How do I achieve this?

Answer 1

u can list it like this, quite easily.

Sub ListAllCF()
    Dim cf As FormatCondition
    Dim ws As Worksheet
    Set ws = ActiveSheet
    For Each cf In ws.Cells.FormatConditions
        Debug.Print cf.AppliesTo.Address, cf.Type, cf.Formula1, cf.Interior.Color, cf.Font.Name
    Next cf
End Sub

buts its buggy as certain type cannot be list using this way so you need to trap the error and find other ways to list the errornous type.

Answer 2

Rosetta's answer is good for typical FormatConditions that are based on expressions but Excel supports other conditional formatting types which were not handled by that routine and caused errors. Here is an updated routine that lists all the conditions on the active sheet. I don't list the details for every type but you can add more as needed. Note that the cf.Operator property only exists on some expressions so I did not include it.

The main difference that makes this code work is the cf variable needs to be declared as Object because Cells.FormatConditions can return multiple data types.

Public Sub ListAllCF()
' List all conditional formatting on the current sheet.  Use for troubleshooting.

    Dim cf As Object ' This can multiple types such as FormatCondition/UniqueValues/Top10/AboveAverage/...
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Debug.Print "Applies To", "Type", "Formula", , "Bold", "Int. Color", "StopIfTrue"
    For Each cf In ws.Cells.FormatConditions
        Debug.Print cf.AppliesTo.Address,
        Select Case cf.Type 'List of Types:  https://docs.microsoft.com/en-us/office/vba/api/excel.xlformatconditiontype
            Case 1, 2, xlExpression
                Debug.Print "Expression", cf.Formula1, cf.Font.Bold, cf.Interior.Color, cf.StopIfTrue
            Case 8, xlUniqueValues
                Debug.Print "UniqueValues", IIf(cf.DupeUnique = xlUnique, "xlUnique", "xlDuplicate"), , cf.Font.Bold, cf.Interior.Color, cf.StopIfTrue
            Case 5, xlTop10
                Debug.Print "Top10", IIf(cf.TopBottom = xlTop10Top, "Top ", "Bottom ") & cf.Rank & IIf(cf.Percent, "%", ""), , cf.Font.Bold, cf.Interior.Color, cf.StopIfTrue
            Case 12, xlAboveAverageCondition
                Debug.Print "AboveAverage", IIf(cf.AboveBelow = xlAboveAverage, "Above Average", IIf(cf.AboveBelow = xlBelowAverage, "Below Average", "Other Average")), , cf.Font.Bold, cf.Interior.Color, cf.StopIfTrue

            '--- Add your own code to support what you want to see for other types.
            Case 3, xlColorScale
                Debug.Print "ColorScale..."
            Case 4, xlDatabar
                Debug.Print "Databar..."
            Case Else
                Debug.Print "Other Type=" & cf.Type

        End Select
    Next cf
    Debug.Print ws.Cells.FormatConditions.count & " rules on " & ws.Name
End Sub

Example output

Applies To    Type          Formula                     Bold          Int. Color    StopIfTrue
$A$1:$S$36    Expression    =CELL("Protect",A1)=0       Null           16777215     False
$B:$B         UniqueValues  xlDuplicate                 True           13551615     False
$H:$H         Top10         Top 10                      Null           13551615     False
$I:$I         Top10         Top 20%                     Null           13551615     False
$J:$J         Top10         Bottom 13%                  Null           13561798     False
$K:$K         AboveAverage  Above Average               Null           13551615     False
$L:$L         AboveAverage  Below Average               Null           10284031     False
$H:$H         ColorScale...
$I:$I         Databar...
$M:$M         Other Type=6
10 rules on Sheet1