How about:

Option Explicit
Sub testme01()

Dim myCount As Long
Dim myCell As Range
Dim myRange As Range

Set myRange = Nothing
On Error Resume Next
Set myRange = ActiveSheet.Cells.SpecialCells(xlCellTypeAllValida tion)
On Error GoTo 0

If myRange Is Nothing Then
MsgBox "no Validation on this sheet"
Exit Sub
End If

'ActiveSheet.CircleInvalid

myCount = 0
For Each myCell In myRange.Cells
If myCell.Validation.Value = False Then
myCount = myCount + 1
End If
Next myCell

MsgBox "Found invalid: " & myCount & vbLf _
& "From: " & myRange.Cells.Count

End Sub

Gareth wrote:

I hve a lot of validation on a sheet, I know that you can highlight invalid
cells by using Tools | Auditing | Show Auditing Toolbar | Circle Invalid
Data

This is very useful but I wondered if it was possible to count the number of
invalid cells and display in a message box as my sheet may contain several
hundred rows.

Thanks in advace.

Gareth

--

Dave Peterson