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
(35% Sale Ends Jan. 26)
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
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 hide a formula in Excel so that a user cannot see it, select it, or change it - th...
Tutorial: How to get a column letter from a number in Excel using a simple formula. This is an ...
Tutorial: Simple way to convert a number stored as text to a number stored as an actual number in E...
Tutorial: 5 simple tips to evaluate any complex formula in Excel! These tips combine to give you th...
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
(35% Sale Ends Jan. 26)
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)