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


Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

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...
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...
Regular Expression Search Formula in Excel - Regex
Tutorial: Part 1 - Regular Expression Match Formula in Excel (No VBA) Part 2 - Complete Excel Reg...