How to print all conditional formatting rules in a worksheet?
It's easy to view all conditional formatting rules in a worksheet by clicking Home > Conditional Formatting > Manage Rules in Excel, but do you know how to print out all condition formatting rules in this worksheet? This article will introduce a VBA to solve this problem.
Recommended Excel Productivity Tools
Please follow below steps to print all conditional formatting rules in a worksheet by VBA.
1. Open the specified worksheet whose conditional formatting rules you will print, and press Alt + F11 keys together to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and then paste below VBA code into the new module window.
VBA: List all conditional formatting rules in active worksheet
Sub M_snb() Dim xRg As Range, xCell As Range Dim xFormat As Object Dim xFmStr, xFmAddress As String Dim xDic As New Dictionary Dim xSpArr, xOperatorArr On Error Resume Next Set xRg = ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions) If xRg Is Nothing Then Exit Sub xDic.Item("Title") = "Type|Typename|Range|StopIfTrue|Operator|Formula1|Formula2|Formula3" If xSpArr.Count = 0 Then xSpArr = Split("Cell Value|Expression|Color Scale|DataBar|Top 10|Icon Sets||Unique Values|Text|Blanks|Time Period|Above Average||No Blanks||Errors|No Errors|||||", "|") xOperatorArr = Split("xlBetween|xlNotBetween|xlEqual|xlNotEqual|xlGreater|xlLess|xlGreaterEqual|xlLessEqual", "|") End If For Each xCell In xRg Set xFormat = xCell.FormatConditions(1) xFmAddress = xFormat.AppliesTo.Address If Not xDic.Exists(xFmAddress) Then xDic.Item(xFmAddress) = xFormat.Type & "|" & xSpArr(xFormat.Type - 1) & "|" & xFmAddress & "|" & xFormat.StopIfTrue If Not IsEmpty(xFormat.Operator) Then xDic.Item(xFmAddress) = xDic.Item(xFmAddress) & "|" & xOperatorArr(xFormat.Operator - 1) End If If Not IsEmpty(xFormat.Formula1) Then xDic.Item(xFmAddress) = xDic.Item(xFmAddress) & "|'" & xFormat.Formula1 End If End If Next If ActiveWorkbook.Worksheets("FmCondictionList") Is Nothing Then Sheets.Add.Name = "FmCondictionList" End If Sheets("FmCondictionList").Cells(1).Resize(xDic.Count) = Application.Transpose(xDic.items) Sheets("FmCondictionList").Columns(1).TextToColumns , , , , 0, 0, 0, 0, -1, "|" End Sub
3. Click Tools > References.
4. In the References – VBAProject dialog box, please check the Microsoft Scripting Runtime option, and click the OK button. See screenshot:
5. Now you return to the module window, please press F5 key or click the Run button to run this VBA.
Now a new worksheet named as “FmCondtionalList” is created and added before the active worksheet. And you will get all conditional formatting rules listing in this worksheet.
6. Click File > Print > Print to print the list of conditional formatting rules.
Recommended Productivity Tools for Excel
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 5 months agoUnfortunately doesn't work (excel 2013).
To post as a guest, your comment is unpublished.· 5 months agoL'algo est mauvais, il n'y a pas de boucle pour couvrir les cas ou un range aurait plusieurs format conditionnels.
To post as a guest, your comment is unpublished.· 10 months agoThis works great, but is there a way to get a loop that includes all tabs?
To post as a guest, your comment is unpublished.· 1 years agoFor me it did create a tab and populate information however it was excluding a lot of the conditional formatting was not included. At the time I ran it I had 112 conditional formatting rules set up but only 8 records displayed in the tab.
To post as a guest, your comment is unpublished.· 1 years agothis does not work. It creates the tab but does not populate the conditional formatting information