Checking Excel 2003 data validation -

The VBA will highlight any cells in a sheet with the following bit data validation errors:

 Range Dims dvError as Range dim dvError error in the form of sub-check validation range (Shotting worksheet) Range Dim RNGDV as the resume start dvError error resume next set rngDV = sht.UsedRange.SpecialCells (XLCellTypeAllValidation) error 0 on GoTo if rngDV is nothing then sht.ClearCircles Else dvError = false cell RngDV for each cell if not cell. Val validation.Value then dvError = true Android for and if next if dvError then sht.CircleInvalid sht.Activate Else sht.ClearCircles End if End if End sub  

However, "for each" loop In fact, lots of data runs in slats with verification.

Does anyone know how to avoid the "loop for everyone" loop or somehow speed it up?

I would have thought that the value of the following 'dvError' would be equal to setting:

  dvError = not rngDV.Validation.Value  

But for some reason, rngDV.Validation.Value is also true, whenever there are data validation errors.

Tried your code and it is working very fast with 4536 cells - as You are breaking yourself for the first time an unverified cell

I tried to take the time out at various points of your code by following:

  Variant tic = now () '... code debug print' ValCount ", rngDV.Cells.Count 'just to see how many cells are in that category ... ... Code debug Print "Pt1", (now () - tick) * 86400000 'display milliseconds' ... code debug. Print "PT2", (Now () - Tic) * 86400000 'Display Milliseconds' ... code debug. Print "PT3", (Now () - Tic) * 86400000 'Display Milliseconds' etc.  

And no scalable delay was found (except when step through the debugger with F8 - of course)

As a normal sign. Try to know how slow your code is and we take it from there.