Select #VALUE cells

[stan]

How to highlight the cells containing #values and #N/A.

 

[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.

 

[stan]

Thank's Dave Peterson

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

 

[mikerickson]

VB is not needed. Conditional formatting can do this. Select A1 and set CF custom function to
=OR(ERROR.TYPE(A1)=3,ERROR.TYPE(A1)=7)

copy this format where it is needed.

 

[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