Allow some columns to be multi select validation, and others single select while looking at column headers instead of number

Question

Thanks to help on here I have this code working perfect, until I decided to change it. I am now wondering how to have this code modified to only allow multi select validation to some columns, and single on the others, but reference validation for all the column headers?

My issue is that when columns are added to removed, the validation doesnot stay with the column it should be in. Can I do both with this same code?

```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 Destination.Column <> Range("SubCategory").Column And Destination.Column <> Range("Area_of_Impact").Column Then GoTo exitError

'If Not Destination.Column = 11 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