Finding cells with errors can be tricky, especially in large workbooks. In this article we're going to show you how to display cell formulas in Excel that contain errors using VBA.
How to display cell formulas in Excel that have errors
VBA has predefined types for the cells that contains special values. These types can be accessed with the SpecialCells method. The SpecialCells method uses two arguments to specify the cells: Type and Value. Using a combination of these two arguments, you can easily access the cell or range type. Below are the values both arguments can get:
Type
- xlCellTypeAllFormatConditions. Cells of any format
- xlCellTypeAllValidation. Cells having validation criteria
- xlCellTypeBlanks. Empty cells
- xlCellTypeComments. Cells containing notes
- xlCellTypeConstants. Cells containing constants
- xlCellTypeFormulas. Cells containing formulas
- xlCellTypeLastCell. The last cell in the used range. Note this will include empty cells that have had any of cells default format changed.
- xlCellTypeSameFormatConditions. Cells having the same format
- xlCellTypeSameValidation. Cells having the same validation criteria
- xlCellTypeVisible. All visible cells
Value
- xlErrors
- xlLogical
- xlNumbers
- xlTextValues
To get the cells containing errors, we can use the xlCellTypeFormulas – xlErrors combination.
After you get the range, you can use the ColorIndex property to set that range a color. Here are some index numbers for colors:
- 3: Red
- 5: Blue
- 6: Yellow
- 0: No Fill
We recommend using On Error Resume Next line at the start of your code in case there are worksheets without any errors.
Please note that you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.
Highlight named ranges
Sub HighlightErrorCells()
On Error Resume Next
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors).Interior.ColorIndex = 3
Next ws
End Sub
