Select #VALUE cells

D

Dave Peterson

If you're looking for all errors...

Select the range you want to inspect
Edit|goto|Special
check formulas (or constants???)
Only leave the Errors option checked
click ok

Change the fill color of those cells????

If you need a macro, you can record one when you do it manually.
 
S

stan

Thank's Dave Peterson

The recorded code for formula is
Selection.SpecialCells(xlCellTypeFormulas, 16).Select
 
D

Dave Peterson

You can use some of excel's constants to make the code a little less cryptic:

Selection.SpecialCells(xlCellTypeFormulas, xlErrors).Select

And there are actually some problems when the selection is a single cell. The
..specialcells stuff will look at the entire sheet.

Your code could be a little more robust:

Option Explicit
Sub testme01()
Dim myRng As Range
Set myRng = Nothing
On Error Resume Next 'in case there are no errors
Set myRng = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas, xlErrors))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No Errors in Selection"
Else
myRng.Select
End If
End Sub
 
Top