PDA

View Full Version : ِAccepting new repeated entries or not



YasserKhalil
07-26-2010, 05:53 AM
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
07-26-2010, 06:08 AM
What does ... to accept the new repeated entries or not ... mean?

YasserKhalil
07-26-2010, 06:11 AM
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
07-26-2010, 06:31 AM
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