Multiple Selection in data validation

Private Sub Worksheet_Change(ByVal Target As Range)

Dim RngDV As Range

Dim OldVal As String

Dim NewVal As String

 

If Target.Count > 1 Then GoTo exitHandler

 

Select Case Target.Column

  Case 5        'this Case line works for column B only

    'Case 2, 5, 6 'this Case line works for multiple columns

    If Cells(Target.Row, 4) = "In" Or Cells(Target.Row, 3) = "Not In" Then

        On Error Resume Next

        'check the cell for data validation

        Set RngDV = Target.SpecialCells(xlCellTypeAllValidation)

        On Error GoTo exitHandler

        If RngDV Is Nothing Then GoTo exitHandler

       

        If Intersect(Target, RngDV) Is Nothing Then

           'do nothing

        Else

            Application.EnableEvents = False

            NewVal = Target.Value

            Application.Undo

           OldVal = Target.Value

            Target.Value = NewVal

            If OldVal <> "" Then

              If NewVal <> "" Then Target.Value = OldVal & ", " & NewVal

            End If

        End If

    End If

End Select

 

exitHandler:

  Application.EnableEvents = True

End Sub