Duplicate value

[mks]

Hi ,

I have the following code, but I want to amend this code to work for column C10:C400 only.

Code -

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TRow As Long
Dim TCol As Long
Dim Foundcell As Range
Dim Ans As VbMsgBoxResult

TRow = Target.Row
TCol = Target.Column
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If WorksheetFunction.CountIf(Cells(TCol).EntireColumn, Target.Value) > 1 Then
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
If Target.Column <> 3 Then Exit Sub
<o> </o>
If WorksheetFunction.CountIf(Cells(TCol).EntireColumn, Target.Value) > 1 Then
Set Foundcell = Columns(TCol).Find(what:=Target.Value, after:=Cells(1, TCol), LookIn:=xlValues, Lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
<o> </o>
Ans = MsgBox("Duplicate Job # : " & Foundcell.Address & " - Press OK to Delete", vbNo)
If Ans = vbYes Then Exit Sub Else ActiveCell.Offset(0, -1).ClearContents

End If
End If
End Sub
Thanks

 

[pedie]

try like this.

[/FONT]
[FONT=Courier New]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C10:C400")) Is Nothing Then
 'You conditions here...
End If
End Sub