How to reference a column header instead of number

Question

I found this code and it works exactly as I want, other than it looks at the column number instead of the header. The issue is if I add a column the vba is then referencing the wrong column and then I have to adjust it.

The overall code is to allow multi select options for data validation on certain columns. Currently it is set to Column 6 and 12. I need it to look at "Sub Category" and "Area of Impact".

Any help is appreciated!!!

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 <> 6 And Destination.Column <> 12 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

Answer 1

Select the “Sub Category” header cell (for example, it is F1), then go to Name Box, which currently displays “F1”, and enter some name: SubCategory for example, and press <Enter>. Then you can use Range("SubCategory").Column instead of 6 in VBA.

The names can be also edited using the Name Manager from Formulas tab.