Excel Conditional Formatting Documentation

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.

Introduction

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

Conditional Formatting Documenter Text File

Here is a screen shot from the text file that this macro created for the sample worksheet, shown above. The list shows:

• Each rule, for each cell with condtional formatting
• Cell address, Condition number, rule, font and fill color (if used)

Conditional Formatting Documenter Macro

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

How to Use the Macro

Here are the steps to follow, after you add the macro to your workbook:

1. Select a sheet with conditional formatting
2. On the Excel Ribbon, click the View tab, then click the Macros command (it's at the far right)
3. Run the macro named CondFormatDocumenter
4. In Windows Explorer, in Excel's default save folder*, open the text file that was created
5. The text file has a list of cells with their conditional formatting rules

*To see where your default save folder is:

• In the Excel Ribbon, click File, then click Options, at the bottom left
• In the list of Option categories, click Save
• In the first section, look in the box for Default local file location.

Get the Free Workbook

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.

More Tutorials

Conditional Format Overview

Conditional Formatting Introduction

Conditional Formatting Based on another cell

Conditional Formatting Examples

Conditional Formatting Data Bars