Select ranges from column headers

[Vivelake]

I have previously written a macro to help me with a spreadsheet that I get for work every week. Some columns need to be hidden, others need conditional formatting. Unfortunately the spreadsheet I get isn't alway exactly the same. The columns that need editing aren't always in the same place, sometimes columns get added and sometimes columns get deleted. I want to edit my macros so they find the header and then select the whole column. I'm not too bothered about new column, I can just hide them manually if need be but I need the Marco to still work even if it can't find a certain header because that column has been deleted that week.

 

[BSALV]

Sub Hide_MyColumns()
     my_hidden_headers = Array("name 1", "name 5", "name 7", "name8")     'array with all the headernames, that need to be hidden

     Set c = Sheets("blad1").Rows(4)                            'in this row are your headers
     For Each My_Header In my_hidden_headers                    'loop through all wanted hidden headers
          r = Application.Match(My_Header, c, 0)                'check if it excists !!!
          If IsNumeric(r) Then c.Cells(1, r).EntireColumn.Hidden = True     'found = hide
     Next

End Sub

 

[Vivelake]

Thank you that works for hiding the columns. I have several macros that use conditional formatting to highlight certain cells. I attached one below. Would you also have a solution so it will still pick that up even if the columns have changed? Column "I" has the header "Predisposed Location" and column "Z" has the header "Due Date", but as it is in the moment as soon as some columns change this macro doesn't work anymore.

Sub Predisposed_20220222()
'
' Predisposed Macro
'
Range("I:I,Z:Z").Select
Range("Z1").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$I1=""Y"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[BSALV]

Sub Hide_MyColumns()
     Dim rPL, rDD

     Set c = Sheets("blad1").Rows(4)                            'in this row are your headers

     my_hidden_headers = Array("name 1", "name 5", "name 7", "name8")     'array with all the headernames, that need to be hidden
     For Each My_Header In my_hidden_headers                    'loop through all wanted hidden headers
          r = Application.Match(My_Header, c, 0)                'check if it excists !!!
          If IsNumeric(r) Then c.Cells(1, r).EntireColumn.Hidden = True     'found = hide
     Next


     my_cf_headers = Array("Predisposed Location", "Due Date")  'array with all the headernames for CF
     i = 0
     For Each My_Header In my_cf_headers                        'loop through all wanted hidden headers
          i = i + 1                                             'pointer to know who is who
          r = Application.Match(My_Header, c, 0)                'check if it excists !!!
          If IsNumeric(r) Then
               If i = 1 Then rPL = r Else rDD = r               'the 1st is Predis... and the 2nd match is Due Date
          End If
     Next

     If IsNumeric(rPL) And IsNumeric(rDD) Then                  'both columns found
          Application.Goto Cells(1, rPL)                        'goto column predisposed
          With Union(Columns(rPL), Columns(rDD))                'these 2 columns
               .Cells.FormatConditions.Delete
               .FormatConditions.Add Type:=xlExpression, Formula1:="=" & Cells(1, rPL).Address(0, 1) & "=""Y"""     'formula
               .FormatConditions(.FormatConditions.Count).SetFirstPriority
               With .FormatConditions(1).Interior               'setup
                    .PatternColorIndex = xlAutomatic
                    .Color = 5296274
                    .TintAndShade = 0
               End With
               .FormatConditions(1).StopIfTrue = False
          End With
     End If
End Sub