Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Detecting Errors in Conditional Formatting Formulas.
Written by Allen Wyatt (last updated June 15, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
Allan uses a lot of conditional formatting, nearly always using formulas to specify the conditions for the formatting. Recently he discovered, by chance, that he had a #REF! error in one of his conditional format formulas. As far as Allan could figure, this was the result of deleting the row of a cell referred to in the formula. The impact is that the conditional formatting won't work for that condition. This has made Allan concerned that there are other instances of conditional formats that have become corrupted since originally being set up. He wonders if there is any simple way of checking all conditional formatting so that these errors can easily be found.
The best way is to use a macro to step through all the conditional formats defined for a worksheet. The following macro does just that, looking for any #REF! errors in the formulas.
Sub FindCorruptConditionalFormat() Dim c As Range Dim fc As Variant Selection.SpecialCells(xlCellTypeAllFormatConditions).Select For Each c In Selection.Cells For Each fc In c.FormatConditions If InStr(1, fc.Formula1, "#REF!", _ vbBinaryCompare) > 0 Then MsgBox Prompt:=c.Address & ": " _ & fc.Formula1, Buttons:=vbOKOnly End If Next fc Next c End Sub
If an error is found, then a message box displays both the address of the cell and the formula used in the conditional formatting rule.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11361) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Detecting Errors in Conditional Formatting Formulas.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
If you have a data table in a worksheet, and you want to shade various rows based on whatever is in the first column, ...
Discover MoreSometimes it can be difficult to figure out the exact formula you should use to properly apply a conditional formatting ...
Discover MoreWhen you apply conditional formatting, you are not limited to using a single condition. Indeed, you can set up multiple ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-06-17 12:08:11
J. Woolley
The Tip's FindCorruptConditionalFormat macro might be more reliable if the following statement
Selection.SpecialCells(xlCellTypeAllFormatConditions).Select
was replaced by this statement
ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllFormatConditions).Select
But here is a more efficient version of the macro:
Sub FindCorruptConditionalFormat2()
Dim fc As FormatCondition
ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllFormatConditions).Select
For Each fc In Selection.FormatConditions
If InStr(1, fc.Formula1, "#REF!", vbBinaryCompare) > 0 Then
MsgBox fc.AppliesTo.Address & ": " & fc.Formula1
End If
Next fc
End Sub
Notice Microsoft's documentation defines xlCellTypeAllFormatConditions as "Cells of any format," but testing demonstrates it actually selects "Cells of any conditional format."
Similarly, Microsoft's documentation defines xlCellTypeSameFormatConditions as "Cells having the same format," but it actually selects "Cells having the same conditional format." For this case the macro's first statement after Dim... should be replaced by
If ActiveCell.FormatConditions.Count = 0 Then Exit Sub
ActiveCell.SpecialCells(xlCellTypeSameFormatConditions).Select
Like many, I’ve been frustrated when my carefully crafted conditional formatting becomes corrupted after innocently performing a copy/paste or some other such sin. And Excel’s miserable Conditional Formatting Rules Manager makes it a chore to fix the mangled rules. So My Excel Toolbox includes macros to backup and restore the active sheet’s conditional formatting using named ranges that auto-adjust to row/column changes. Run the CFBackup macro before your conditional formatting rules are broken. If they later become broken, run CFRestore.
My Excel Toolbox also includes the following dynamic array function:
=ListFormatConditions([AllSheets], [SkipHeader])
This function returns Applies To Range, Type, and Stop If True for each conditional format. When using pre-2021 versions of Excel without support for dynamic arrays, review the PDF file UseSpillArray.pdf.
See https://sites.google.com/view/MyExcelToolbox/
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments