Private Sub Worksheet_Change(ByVal Target As Range) 'SAS
Dim r As Long
Dim lc As Long
Dim ans As String
Dim rngDV As Range
If Target.Count 1 Or Target.Column < 3 Then Exit Sub

'Me.Unprotect Password:="builder"
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
If Intersect(Target, rngDV) Is Nothing Then
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
Exit Sub
End If

r = Target.Row
lc = Cells(r, Columns.Count).End(xlToLeft).Column + 1
Application.EnableEvents = False
Cells(r, lc) = Target
Application.EnableEvents = True
If Application.CountIf(Range(Cells(r, "d"), Cells(r, "Q")), Target) 1 Then
ans = MsgBox("Duplicated, Continue?", vbYesNo)
If ans = vbNo Then
Cells(r, lc) = ""
End If
Target = ""
End If
'Me.Protect Password:="builder"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"winnie123" wrote in message
...
Thank Don,

I have sent the file.

best regards

Winnie

"Don Guillett" wrote:

without seeing, I'm still confused.
If desired, send your file to my address below along with this msg
and
a clear explanation of what you want and before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"winnie123" wrote in message
...
Sorry,

I will be more specific.

I'm using Excel 2003

What I want to do is check for duplicates on the same row.

C4 is data validation and when a value is selected it goes to D4, C4
then
clears contents, then I can select again from the list it enters the
value
in
E4 and so on until Q4.

The next row would be row 7, then row 10 etc until row 34

What I need to do is prevent duplicate selection. So if duplicated
entry
is
found then message box to appear, "you have already made that selection
do
you wish to continue?" then have Yes/No so that it either exists the
sub
or
clear the contents of that cell.

I have tried data validation on the range D4:Q4 as per the tips on
Contextures web Site, but that did not work. So was looking at code to
see
if
i could achieve this by code.

Hope this is enough info to go on.

Thanks

Winnie

"Don Guillett" wrote:

Let's be clear about what you want. When you change a cell in one
cell(????)
or range(?????), you want to check column(??????) and if a match show
the
duplicate??

"if I change cell a2, I want to look in a3 down for a match"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"winnie123" wrote in message
...
Hi,

I have found this code in this forum, which checks to see if a value
has
been duplicated, how do I change to look at a row instead of a
column?

I have tried changing the myColumn to myRow As String="4"
and the other reference referring to Column as row, but it does not
work.

Any ideas?

Thanks
Winnie

Private Sub Worksheet_Change(ByVal Target As Range)
'Adjust next constant to your own needs
Const myColumn As String = "B"
Dim rng As Range
Dim Found As Range

Set rng = UsedRange.Columns(myColumn)
If Intersect(Target, rng) Is Nothing _
Or Target.Value = "" _
Then Exit Sub
Set Found = rng.Find(Target.Value)
If Found.Address < Target.Address Then
Target.Select
MsgBox ("Duplicate code")
End If
End Sub