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

The following macro counts the data validation cells with errors:

Sub CountDVErrors()
Dim rngDV As Range
Dim c As Range
Dim countDV As Long
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler
Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

countDV = 0
For Each c In rngDV
If Not c.Validation.Value Then
countDV = countDV + 1
End If
Next

MsgBox "There are " & countDV & " DV cells with errors"
Exit Sub

errHandler:
MsgBox "No cells with data validation on the active sheet."

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




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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