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