This conditional formatting documenter macro creates a text file in Excel's default Save folder, with a list of the active worksheet's conditional formatting rules, and the font and fill colours for each affected cell. Get the example workbook to try the macro.
The following Conditional Formatting Documenter macro creates a text file with a list of the active worksheet's conditional formatting, and the Font and Fill colours that are used for the cell formatting rules.
Here is one of the worksheets from the sample file that you can get, below. There are 2 conditional formatting rules, which refer to the High and Low values in cells I1 and I2, and give some cells green or orange fill colour, based on the rules.
NOTE: The set up instructions for this conditional formatting is on the Conditional Formatting Intro page
Here is a screen shot from the text file that this macro created for the sample worksheet, shown above. The list shows:
First, copy the macro code, below, onto a regular code module in a workbook. This macro is also in the sample workbook, and you can test it there, before using it in your own Excel files.
The macro finds all the cells with conditional formatting, and creates a text file with the details for each rule. See the next section for steps on how to use the macro.
Thanks to J.E. McGimpsey who wrote this code.
Sub CondFormatDocumenter() 'adapted from data validation code posted by J.E. McGimpsey 'https://www.mcgimpsey.com/excel/index.html 'Excel conditional formatting documentation Dim sCF(0 To 2) As Variant Dim rCF As Range Dim rCell As Range Dim iCF As Integer Dim nFile As Long Dim sC As String Dim strCF As String Dim strInteriorColor As String Dim strFontColor As String sC = vbTab On Error Resume Next Set rCF = Cells.SpecialCells(xlCellTypeAllFormatConditions) On Error GoTo 0 If Not rCF Is Nothing Then nFile = FreeFile Open "test.txt" For Output As #nFile For Each rCell In rCF iCF = rCell.FormatConditions.Count For iCF = 1 To iCF With rCell.FormatConditions(iCF) sCF(0) = Choose(.Type, "Cell Value Is", "Formula Is") sCF(1) = .Formula1 On Error Resume Next sCF(2) = .Formula2 On Error GoTo 0 Select Case .Type Case xlCellValue Select Case .Operator Case xlAnd strCF = "Between" & sC & sCF(1) _ & sC & "And" & sC & sCF(2) Case xlNotBetween strCF = "Not Between" & sC & sCF(1) _ & sC & "And" & sC & sCF(2) Case xlEqual strCF = "Equal to" & sC & sCF(1) Case xlNotEqual strCF = "Not Equal to" & sC & sCF(1) Case xlGreater strCF = "Greater Than" & sC & sCF(1) Case xlLess strCF = "Less Than" & sC & sCF(1) Case xlGreaterEqual strCF = "Greater Than or Equal to" _ & sC & sCF(1) Case xlLessEqual strCF = "Less Than or Equal to" _ & sC & sCF(1) Case Else 'do nothing End Select Case xlExpression strCF = sCF(1) Case Else strCF = sCF(1) End Select If .Interior.ColorIndex > 0 Then strInteriorColor = sC & "Interior: " _ & .Interior.ColorIndex Else strInteriorColor = "" End If If .Font.ColorIndex > 0 Then strFontColor = sC & "Font: " _ & .Font.ColorIndex Else strFontColor = "" End If strCF = sC & "Cond " & iCF & ": " _ & sCF(0) & sC & strCF _ & strInteriorColor & strFontColor End With Print #nFile, rCell.Address(False, False) _ & strCF Erase sCF Next iCF Next rCell Close #nFile End If End Sub
Here are the steps to follow, after you add the macro to your workbook:
*To see where your default save folder is:
Click here to get a zipped sample file for this tutorial. The zipped file is in xlsm format, and it contains a macro. When you open the workbook, be sure to enable macros, if you want to test the macro.
Last updated: February 26, 2022 12:51 PM