Have a lot of errors in your worksheet but having a hard time finding them? Excel Macros will make short work of this and we can highlight all errors!

Make sure your Excel has the Developer Tab enabled following this tutorial.

I explain how you can do this below step by step!

What does it do?

Highlight all of the errors

Copy Source Code:


Sub HighlightAllErrors()

Dim cell As Range

For Each cell In ActiveSheet.UsedRange
'Check if it is an error, then change the style to be Bad
If WorksheetFunction.IsError(cell) Then
cell.Style = "Bad"
End If
Next cell

End Sub

Final Result: 

Highlight All Errors Using Macros In Excel | MyExcelOnline

Exercise Workbook:

Highlight All Errors Using Macros In Excel | MyExcelOnline

DOWNLOAD EXCEL WORKBOOK


Here is our initial set of data, you can see there are a lot of errors in there:

Highlight All Errors Using Macros In Excel | MyExcelOnline

STEP 1: Go to Developer > Code > Visual Basic

Highlight All Errors Using Macros In Excel | MyExcelOnline

 

STEP 2: Paste in your code and Select Save. Close the window afterwards.

Highlight All Errors Using Macros In Excel | MyExcelOnline

 

STEP 3: Let us test it out!

Open the sheet containing the data. Go to Developer > Code > Macros

Highlight All Errors Using Macros In Excel | MyExcelOnline

 

Make sure your macro is selected. Click Run.

Highlight All Errors Using Macros In Excel | MyExcelOnline

 

With just one click, all of the cells with errors are now highlighted!Highlight All Errors Using Macros In Excel | MyExcelOnline

 

How to Highlight All Errors Using Macros In Excel

 

HELPFUL RESOURCE:

101 Macros Book

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn