Multiple select validation list VBA

[JackReacher85]

Good afternoon,

Thank you for taking a look at my question i have the below VBA code i managed to pull from a colleagues spreadsheet, what i need though is for this to apply only to cells in Column M from Cell M2 onward. It may be worth noting that my data is in a table.

Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
Dim DelimiterType As String
DelimiterType = ", "
 
If Destination.Count > 1 Then Exit Sub
 
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
 
If rngDropdown Is Nothing Then GoTo exitError
 
If Intersect(Destination, rngDropdown) Is Nothing Then
   'do nothing
Else
  Application.EnableEvents = False
  newValue = Destination.Value
  Application.Undo
  oldValue = Destination.Value
  Destination.Value = newValue
    If oldValue <> "" Then
    If newValue <> "" Then
        If oldValue = newValue Or _
            InStr(1, oldValue, DelimiterType & newValue) Or _
            InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then
            Destination.Value = oldValue
                Else
            Destination.Value = oldValue & DelimiterType & newValue
        End If
    End If
    End If
End If
 
exitError:
  Application.EnableEvents = True
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
End Sub

I would appreciate any help you can offer

 

[jkpieterse]

Replace:

Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)

with:

Set rngDropdown = Intersect(Range("M:M"), Range("M2").ListObject.DataBodyRange)