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
NOTE: Conditional Formatting documentation is also available in my Excel Tools add-in. It creates a new sheet in the workbook, with a list of the formatting rules.
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: Conditional Formatting documentation is also available in my Excel Tools add-in
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 '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
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.
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 Contextures Sites
Last updated: November 6, 2017 3:14 PM
Contextures RSS Feed