Data validation - prevent duplicates

[kiwikiki718]

Hi I have a dropdown list I created using the data validation option based on list I have in another sheet.

I was wondering if I could also prevent users from selecting a duplicate value from the dropdown list within the same cells.

 

[kiwikiki718]

You can use the worksheet change event ... something along these lines:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Const TARGET_RANGE_ADDRESS = "A1:A5,A10:A15,A20:A25"

    On Error GoTo RestoreAppEvents
    With Application
    If Not (.Intersect(Target, Range(TARGET_RANGE_ADDRESS)) Is Nothing Or IsEmpty(Target.Cells(1&))) Then
        If IsDuplicate(Range(TARGET_RANGE_ADDRESS), Target.Text) Then
            .EnableEvents = False
            .Undo
            MsgBox "Duplicates not allowed in range : " & TARGET_RANGE_ADDRESS, vbCritical
        End If
    End If
RestoreAppEvents:
    .EnableEvents = True
    End With

End Sub

Private Function IsDuplicate(ByVal Rng As Range, ByVal InputVal As String) As Boolean
    Dim i As Long, n As Long
    For i = 1& To Rng.Areas.Count
        n = n + Evaluate("=COUNTIF(" & Rng.Areas(i).Address & ",""" & InputVal & """)")
    Next i
    IsDuplicate = n > 1&
End Function

Thank you this works but I would like to treat the duplicates within the 3 ranges independent. For example user will not be able to select the same value within range A1:A5 only. Same for A10: to A15 only etc.