List all Conditional Formatting Formulas in Excel

Add to Favorites
Author:

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.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
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.)

View Course

Similar Content on TeachExcel
Make Complex Formulas for Conditional Formatting in Excel
Tutorial: How to make complex formulas for conditional formatting rules in Excel. This will serve as...
Complex Structured References (Table Formulas) in Excel
Tutorial: How to use complex structured references, table formulas, in Excel. If you don't already...
Join Parts of a List With a Single Formula in Excel - (1 Minute Tutorials)
Tutorial: How to quickly combine any part of an entire list of values into a single value in a sing...
Hide Formulas in Excel
Tutorial: How to hide a formula in Excel so that a user cannot see it, select it, or change it - th...
Convert Column Number to Letter Using a Formula in Excel
Tutorial: How to get a column letter from a number in Excel using a simple formula. This is an ...
Text to Number Format Formula in Excel
Tutorial: Simple way to convert a number stored as text to a number stored as an actual number in E...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
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.)

View Course