Contextures

Excel Conditional Formatting Documentation

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

Introduction

Thanks to J.E. McGimpsey for generously sharing his code.

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.

NOTE: This feature is available in my Contextures Excel Tools add-in

conditional formatting

Conditional Formatting Documenter Macro

Code the following code onto a regular code module in a workbook.

Sub CondFormatDocumenter()
  'adapted from data validation code posted by J.E. McGimpsey
  'http://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

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search

Search Contextures Sites

More Tutorials

Conditional Formatting Introduction

Conditional Formatting Based on another cell

Conditional Formatting Examples

Conditional Formatting Data Bars

Search Contextures Sites

 

Excel Tools Add-in

 

 

 

Excel Data Entry Popup List

 

Last updated: October 21, 2016 7:33 PM
Contextures RSS Feed