PROBLEM - HELP HELP - Prevent Duplicate entries on txt field

[dspa0712]

I have a speadsheet where Column C (C1:C5) currently holds values

C1=A
C2=FF
C3=fd
C4=gg
C5=CF

What I what I want is a peice of code to run as a check so for example when a user enters a value in C6 it will check that the value has not already been entered in (C1 toC5)..if so then a message will appear this number already has been entered.

Then if a user enters into C6 the value kj, when a new users enters a value in C7 the next time it will check whether this new value has been entered in (C1 to C6) etc etc.

It would also be ideal if the checking ignore cases...eg values like CF and cf were classed as the same and couldn't be entered.


Please Please help

 

[Lewiy]

Ah, I wonder if it's recursing, try this:

Private Sub Worksheet_Change(ByVal Target As Range) 
Application.EnableEvents = False
With WorksheetFunction 
    If .CountIf(Range("C1:C" & Target.Row), Target.Value) > 1 And Target.Column = 3 Then 
        MsgBox "This item has already been entered" 
        Target.Value = "" 
    End If 
End With
Application.EnableEvents = True 
End Sub

EDIT: Oops, put that in the wrong place!!

 

[dspa0712]

Think the Young Apprentice has worked it out by pure chance !!!!
_________________________________________________
Private Sub Worksheet_Change(ByVal Target As Range)
With WorksheetFunction
If .CountIf(Range("C1:C" & Target.Row), Target.Value) > 1 And Target.Column = 3 Then
MsgBox "This item has already been entered"
Target.Select
clear
End If
End With

End Sub

Sub clear()
ActiveCell.clear
End Sub
_______________________________________


It works !!!!!!!!!!!!