Prevent User Enter Duplicates in Column

I have a spreadsheet that users need to enter part numbers that we have quoted for. I need to prevent users from entering duplicates in column C. Data validation isn't an option since users copy & paste sometimes into that column.

The code works fine if user happens to copy & paste a part number already on that list. But for some reason, sometimes the code will work & sometimes it fails to catch duplicates when users type in the part numbers. Sometimes users will insert a row & type a part number & the code won't catch the duplicate part number.

Need help with the code to catch any duplicates in column C & sometimes the data in column C is non contiguous so the code has to check if the data entered matches any cell in that column. I would also like for the message box to also display the cell location were the original data is located.

Below is the code used & I also attached a sample of my spreadsheet.

Any help is greatly appreciated!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range
Dim ans As String

Const myCol As Long = 3

If Intersect(Target, Columns(myCol)) Is Nothing Then Exit Sub

Application.EnableEvents = False

For Each r In Intersect(Target, Columns(myCol))
   If Application.CountIf(Columns(myCol), r.Value) > 1 Then
   MsgBox (r.Value & " already exists")
       r.ClearContents
   End If
Next

Application.EnableEvents = True

End Sub