Jim
Thanks for that. That appears to be just like what I have. The only
difference that I see is that my range is non-contiguous. My range is:
Set TheRng = Range("D1,F1,H1,J1,L1,N1")
Would that cause the "Type Mismatch" error?
If so I could just loop through the range and count the number of
Target.Value
I just changed my range to a contiguous range and it worked, so that answers
my question.
Thanks for your time. Otto
"Jim Rech" wrote in message
...
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