Excel Conditional Formatting Documentation

This macro creates a text file in Excel's default Save folder, with a list of the active worksheet's conditional formatting, including the font and fill colours selected

Introduction

The following procedure creates a text file (in the active folder) with a list of the active worksheet's conditional formatting, and the Font and Fill colours selected.

Conditional Formatting Documenter Macro

Code the following code onto a regular code module in a workbook. Thanks to J.E. McGimpsey for generously sharing his 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

Download the Free Workbook

Click here to download 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.

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

More Tutorials

Conditional Formatting Introduction

Conditional Formatting Based on another cell

Conditional Formatting Examples

Conditional Formatting Data Bars