How to save conditional formatting in Excel

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 an

How to save conditional formatting in Excel

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 example from sample file

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 example from sample file

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.
default save location for Excel

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

Video liên quan