Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
List all Conditional Formatting Formulas in Excel
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.
Macro to List Conditional Formats
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
How to Use the Macro
Install the macro into the workbook and then run it. That's it.
The output will look like this:
Notes
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.
Question? Ask it in our Excel Forum
Tutorial: How to make complex formulas for conditional formatting rules in Excel. This will serve as...
Tutorial: How to use complex structured references, table formulas, in Excel. If you don't already...
Tutorial: How to get a column letter from a number in Excel using a simple formula. This is an ...
Tutorial: 5 simple tips to evaluate any complex formula in Excel! These tips combine to give you th...
Tutorial: Formulas to determine if the current cell is odd or even; this allows you to perform speci...
Tutorial: How to Sum values using an OR condition across multiple columns, including using OR with ...
