The code below uses the special cells method to find each validation list on
the worksheet. Then checks to see if the valkue in the cell matches the
validation list. The validatioin list has an equal sign in front of the
address range so I remove it wih the mid statement. Finally I use a FIND
method for checking if the data in he cell matches any value in the
validation list. If it doesn't a highlight the cell.

Sub test()

With Sheets("Sheet1")
Set Validatecells = .Cells.SpecialCells(Type:=xlCellTypeSameValidation )
For Each cell In Validatecells
'remove equal sign from formula
validationRange = Mid(cell.Validation.Formula1, 2)
Set c = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub


"Minitman" wrote:

Greetings,

I am using Excel from Office 2003 on an XP box.

I have several workbooks that use a drop down validation list to pick
name from an other workbook of names. It is working fine. The
problem is that the workbook of names called MCL.xls is being updated
due to errors in the list. When an entry in MCL.xls changes, the drop
down validation list no longer matches the value in the validation
drop down cell.

I need a way to identify these unmatched entries when I open each so
that I can manually find the new corrected entry from the drop down
list.

Anyone have any ideas?

Any help will be appreciated.

-Minitman