Alice,

You're welcome.

I have a hard time remembering to look at which types of cells can be
isolated using SpecialCells but it can be a very handy tool. The
'Intersection' method was something I ignored until the first time I decided
I had to use it and realized how many earlier tasks could have been made
easier if I'd researched a bit sooner.

There is a similar 'Union' method that allows you to combine ranges into a
bigger range. We could have cycled through the cells in the UsedRange and
used 'Union' to build a new range consisting of all the unlocked cells and
then used the Intersect method to find cells that were both unlocked and set
for data validation but I thought the nested If...Then...Else statements
might be easier to follow.

Steve


"my-wings" wrote in message
...
Steve:

This is sweet!

I knew there had to be some way to "ask" Excel if a cell had data
validation, because I found the instructions about applying data
validation that say you need to "modify" a cell if data validation already
exists, otherwise you have to "add". It didn't make any sense that there
was no way to query a cell about it's data validation status. It looks
like this "SpecialCells" method provides a way.

And the "Intersection" method is something that looks very handy indeed.
I'm sure I will be using that a lot, now that my eyes have been opened.

Thanks so much for suggesting this different solution to the problem.

Alice
(aka night_writer)


"Steve Yandl" wrote in message
. ..
Alice,

This doesn't show you anything about error capture but it is an alternate
approach to your task.

_________________________________

Sub test()
Dim rngValidation As Range
Dim rngTemp As Range

Set rngValidation =
ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllVa lidation)

For Each rngTemp In ActiveSheet.UsedRange.Cells
If Not rngTemp.Locked Then
If Application.Intersect(rngTemp, rngValidation) Is Nothing Then
rngTemp.Interior.Color = RGB(255, 255, 153)
Else
If rngTemp.Validation.ShowError Then
rngTemp.Interior.Color = RGB(255, 204, 153)
Else
rngTemp.Interior.Color = RGB(255, 255, 153)
End If
End If
End If
Next rngTemp

End Sub
_________________________________

Steve