List all Conditional Formatting Formulas in Excel

Add to Favorites

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


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:


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

Downloadable Files: Excel File

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...
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 ...
Regular Expression Search Formula in Excel - Regex
Tutorial: Part 1 - Regular Expression Match Formula in Excel (No VBA) Part 2 - Complete Excel Reg...
5 Tips for Evaluating Complex Formulas in Excel
Tutorial: 5 simple tips to evaluate any complex formula in Excel! These tips combine to give you th...
Sum Values that Equal 1 of Many Conditions across Multiple Columns in Excel
Tutorial: How to Sum values using an OR condition across multiple columns, including using OR with ...