Search Contextures Sites
Custom Search

Excel Conditional Formatting Documentation

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.

How to Document a Worksheet's Conditional Formatting

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

Download the zipped sample file

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

          

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

Excel Conditional Formatting Tutorials

Excel Conditional Formatting -- Introduction

Excel Conditional Formatting -- Based on another cell

Excel Conditional Formatting -- Examples  

Excel Conditional Formatting -- Data Bars  

 

 

Related Tutorials

Conditional Formatting -- Based on another cell

Conditional Formatting -- Examples

Conditional Formatting Documentation  

Conditional Formatting -- Data Bars  

 

 

 

 

 

 

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.

 

Last updated: April 6, 2016 4:39 PM