warning for duplicate value in a specific column

[Bijan2048]

Hi experts
I am looking for a VB script to control cells of a specific column( B ) that user Don't enter a duplicate value

A B C
MC 123
DF 456
DF 789
LG 123 ====> WARNING!! Duplicate value

and Don't let the user to continue unless change it to unique value
Thanks in advance

 

[AlanY]

try this in the sheet where you want to enforce the no-duplicate rule for column B.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range
    Dim value As String
    
    Set rng = Range("B:B") ' Specify the range of the column to check for duplicates
    
    If Not Intersect(Target, rng) Is Nothing Then ' Check if the changed cell is in the specified range
        For Each cell In Intersect(Target, rng)
            value = cell.value
            If WorksheetFunction.CountIf(rng, value) > 1 Then
                MsgBox "Duplicate value detected in column B: " & value
                Application.EnableEvents = False ' Disable events to prevent infinite loop
                cell.ClearContents ' Remove the duplicate value
                Application.EnableEvents = True ' Re-enable events
            End If
        Next cell
    End If
End Sub

 

[mumps]

You could do this using data validation in column B. Simply highlight column B. Click on Data Validation .... Custom and enter this formula in the formula box: =(COUNTIF($B$1:$B1, $B1)<2)