hi

got a situation wherby in column A, there is a list of values for the
user to select using data validation list,

need to prevent the user from selecting 2 similar data in any of the
cells in column A of entire workbook

a error message has to appear to warn the user if such a situation
arises and then point to that cell value in a workbook

any idea how to do it?

Not sure the code i am using below is right...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim wsLoop As Worksheet

If Intersect(Target, Range("A2:A200")) Is Nothing Then Exit Sub

For Each wsLoop In ThisWorkbook.Worksheets
If Not wsLoop.Name = "Sheet1" Then
If WorksheetFunction.CountIf(wsLoop.Range("A2:A200"),
Target) 0 Then
MsgBox "That entry already exists in the " +
wsLoop.Name + " sheet"
Application.EnableEvents = 0
Target.ClearContents
wsLoop.Select
Application.EnableEvents = 1
End If
End If
Next wsLoop

End Sub


- Thanks