Determining invalid cells (data validation, vba)

[Nigel]

I have a series of cells with validation defined using the
data->validation option.

Some of the cells can be invalid to start with.

I can use the audit tool bar to circle the cells which are
invalid.

How can I determine which cells are invalid using VBA?

Thanks

Nigel

 

[BrianB]

I would do something using =VLOOKUP() to a list of valid entries

 

[Debra Dalgleish]

You can check the validation property:

'=======================
Sub ColourValidationCells()
'If validation value for the cell is false,
'colour the cell red
Dim rng As Range
Dim c As Range

On Error GoTo errhandler
Set rng = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

For Each c In rng
If Not c.Validation.Value Then
c.Interior.ColorIndex = 3
Else
c.Interior.ColorIndex = xlNone
End If

Next
Exit Sub

errhandler:
MsgBox "No cells with data validation"

End Sub
'===============================

 

[Nigel]

Thanks Debra

Validation.Value solved the problem.