Corrected this code to prevent Duplicate entry in three columns

[Hany ali]

Hello my Dears ... I Want Your help To Correct this Code to do well ,this Code To Prevent Duplicat Entry In Three Columns ( B,C,D) and please Note Two Columns Have DropDown List
and some time , i have two Differant Guides In the same Date and the Same Trip
For example, the row colored red, I cannot enter it in the presence of this code .. But I must be able to enter it because this guide’s trip on this date is not repeated

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Column = 3 Or Target.Column = 4 Then
If Application.CountIf(Columns(Target.Column), Target) > 1 Then
MsgBox "Duplicate Entry Not Allowed!", vbCritical
Target.ClearContents
End If
End If
End Sub

 

[Marc L]

Hello,​

​

explain what is a duplicate on your side, which criterion to apply ?​

​

 

[Hany ali]

thanks Alot... I mean Duplicate For Three Columns ,for Example I don't Want Duplicat for the Same Guide Name In the Same Date for the Same Trip Name

 

[Marc L]

So according to Excel basics with the appropriate worksheet function aka COUNTIFS rather than COUNTIF :​

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
            If .CountLarge > 1 Or .Row = 1 Then Exit Sub
            If Intersect(UsedRange.Columns("B:D"), .Cells) Is Nothing Then Exit Sub
            If Application.CountA(Cells(.Row, 2).Resize(, 3)) < 3 Then Exit Sub
        If Evaluate(Replace(Replace("COUNTIFS(B2:B#,B¤,C2:C#,C¤,D2:D#,D¤)>1", "#", Cells(Rows.Count, 2).End(xlUp).Row), "¤", .Row)) Then
            MsgBox "This record already exists", 48
            Application.EnableEvents = False
           .ClearContents
           .Select
            Application.EnableEvents = True
        End If
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Hany ali]

Well done, that is really what is required