Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Learn Access Now
Free Printable Forms
Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site
Adding a Little Animation to Your Life
Converting a Range of URLs to Hyperlinks
Making the Formula Bar Persistent
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()
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5730) applies to Microsoft Excel versions: 97 2000 2002 2003 2007
A Picture is Worth Thousands! Your worksheets are not limited to holding numbers and text. You can also add graphics or easily create charts based on your data. Excel Graphics and Charts, available in two versions, helps you make your graphics and charts their absolute best.