This variation on what you're doing works okay for me:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TheRng As Range
Set TheRng = Range("A1:A10")
With Application
If .CountIf(TheRng, Target.Value) 1 Then
.EnableEvents = False
.Undo
.EnableEvents = True
End If
End With
End Sub


--
Jim
"Otto Moehrbach" wrote in message
...
Excel XP & Win XP
I have a range, TheRng, of cells all formatted as text. The user enters
numbers into those cells. I want to trap the instance of him entering a
number that has already been entered somewhere else in TheRng. I am
trying to use the CountIf worksheet function in a Worksheet_Change macro
as follows:
If Application.CountIf(TheRng, Target.Value) 1 Then....
Or
If Application.CountIf(TheRng, CStr(Target.Value)) 1 Then....
Or
If Application.CountIf(TheRng.Address, CStr(Target.Value)) 1 Then....
Each try has produced a "Type Mismatch" error.
How can I get this to work? Thanks for your time. Otto