ِAccepting new repeated entries or not

[YasserKhalil]

Hi guys

I have an attachment which colours the repeated entries in Columns A and B
I want to add some lines of code that give me a message box to accept the new repeated entries or not
The code is :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Integer, C As Integer
If Not Intersect(Target, Range("A1:B100")) Is Nothing Then
    Application.ScreenUpdating = False
    C = Target.Column
    For R = 1 To 100
        If Cells(R, C) <> "" And Application.CountIf(Range(Cells(1, C), Cells(100, C)), Cells(R, C)) > 1 Then
            Cells(R, C).Interior.ColorIndex = 40
        Else
            Cells(R, C).Interior.ColorIndex = xlNone
        End If
    Next
    Application.ScreenUpdating = True
End If
End Sub

[Bob Phillips]

What does ... to accept the new repeated entries or not ... mean?

[YasserKhalil]

For example, if I entered the value 1 in cell A15
The message should appear and ask me to accept this new entry (Don't forget it's repeated value) or not .. If not, the cell value must be cleared

[Bob Phillips]

[VBA]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:B100"

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Application.ScreenUpdating = False

If Application.CountIf(Me.Range(WS_RANGE), Target.Value2) > 1 Then

If MsgBox("Accept Duplicate?", vbYesNo, "RepeatedValues") = vbYes Then

Target.Interior.ColorIndex = 40
Else

Target.Value2 = ""
End If
Else

Target.Interior.ColorIndex = xlNone
End If

Application.ScreenUpdating = True
End If
End Sub
[/VBA]