List all conditional formatting formulas in a worksheet in Excel.
This allows you to quickly view and manage all of the formulas that you use for your conditional formatting rules. It will get the conditional formatting rules for the entire workbook and put them on their own new worksheet at the end of the workbook.
Option Explicit
Sub List_Conditional_Formatting_Rules()
Dim ws As Worksheet, wsCF As Worksheet, NR As Long
Dim CFrule As FormatCondition, Rng As Range
On Error Resume Next
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "CF Rules"
Set wsCF = Sheets("CF Rules")
wsCF.Range("A1:C1").Value = [{"Sheet","Formula","Range"}]
NR = 2
For Each ws In Worksheets
If ws.Name <> "CF Rules" Then
Set Rng = ws.Cells
For Each CFrule In Rng.FormatConditions
wsCF.Range("A" & NR).Value = ws.Name
wsCF.Range("B" & NR).Value = "'" & CFrule.Formula1
wsCF.Range("C" & NR).Value = CFrule.AppliesTo.Address
NR = NR + 1
Next CFrule
End If
Next ws
wsCF.Columns.AutoFit
End Sub
Install the macro into the workbook and then run it. That's it.
The output will look like this:
This is a great macro to help manage a large Excel file and to figure out what is going on if you have a lot of conditional formatting rules.
Download the associated workbook attached to this tutorial to get the macro already in Excel.