how change code to implementing for multiple columns instead of one

[Ali M]

Hello
I have this code prevent adding duplicates items for column D , but I want to change it to multiple columns ( D:H , K:M )

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 4 And Len(Target.Value) > 0 Then
    Application.EnableEvents = False
    If WorksheetFunction.CountIf(Range("D:D"), Target.Value) > 1 Then
        MsgBox Target.Value & " is a duplicate entry.  It will be removed.", vbExclamation, "Data Entry Editor" & "(INDEX(C7:C23,MATCH(Target.Value,D7:D23,0))"
        Target.ClearContents
    End If
    Application.EnableEvents = True
End If

End Sub

thanks

 

[Albert 1]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("D:M"), Target) Is Nothing Then
With Target
If Len(.Value) > 0 And (.Column < 9 Or .Column > 10) Then
Application.EnableEvents = False
If WorksheetFunction.CountIf(Range("D:M"), .Value) > 1 Then
MsgBox .Value & " is a duplicate entry, it will be removed.", vbExclamation
.ClearContents
End If
Application.EnableEvents = True
End If
End With
End If
End Sub

You cannot maintain the header of the msgbox with 'index' and match'.

 

[Ali M]

thanks .should be for each individall column I mean each column should accept the item from the first time even if there is duplicated item in another column just prevent duplicates items in the same column for each column

 

[Albert 1]

should be for each individall column I mean each column should accept the item from the first time even if there is duplicated item in another column

That was not clear in your first post, because there you wrote:

I want to change it to multiple columns ( D:H , K:M )

For each individual column, you can use:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("D:M"), Target) Is Nothing Then
Dim myrange As Range
With Target
If Len(.Value) > 0 And (.Column < 9 Or .Column > 10) Then
Set myrange = Columns(.Column)
Application.EnableEvents = False
If WorksheetFunction.CountIf(myrange, .Value) > 1 Then
MsgBox .Value & " is a duplicate entry, it will be removed.", vbExclamation
.ClearContents
End If
Application.EnableEvents = True
End If
End With
End If
End Sub

 

[Ali M]

That was not clear in your first post, because there you wrote:

my apologies !!

For each individual column, you can use:

much appreciated for your assistance .