Format header row

July 11, 2008 – 8:57 pm by JP

So I was getting tired of constantly formatting the header row on whatever spreadsheet I am working on, hence this short sub was born. It pretties up the header row and adds autofilter arrows. Most importantly it autosizes the columns so all the information is visible. I chose Teal (ColorIndex 14) but if you want to change that, check out Dave McRitchie’s excellent color palette page for a list of the colors you can use.


Sub FormatHeaderRow()

Application.ScreenUpdating = False

If ActiveSheet.AutoFilterMode = False Then
    With Range(Range("A1"), Range("IV1").End(xlToLeft))
        .AutoFilter
        .Font.ColorIndex = 2
        .Font.Bold = True

        With .Interior
            .ColorIndex = 14
            .pattern = xlSolid
        End With

        .HorizontalAlignment = xlCenter
        .Columns.AutoFit
    End With

Else
    MsgBox "Cannot autofilter the header row, there is already an autofilter on this sheet", vbCritical
End If

Application.ScreenUpdating = True
End Sub

If you want a different color, just edit the number after the .ColorIndex property, using Dave’s page as a guide. Note that the text color is changed to white, which might not work with all backgrounds. You can comment out or remove that line to leave the text color unchanged (or change it to 1, which according to Dave’s page, is the ColorIndex number for the color black).

Also note that if you have blank columns or empty cells in the topmost row, this sub will still color and add autofilter arrows to them as well. If you want to edit this behavior, you could change the With statement to something like


    With Range(Range("A1"), Range("A1").End(xlToRight))

This would only edit the range from A1 to whatever cell would be selected if pressed Ctrl-Right Arrow.

ps- I am still working on a sub to export contact information from Outlook, but I decided to turn it into a full-blown Excel add-in after working on some code that was requested by a visitor to the site. So now I’m working on an add-in for Excel that you can use to export contacts, tasks, and appointments/meetings to Excel. Right now I am building the userform and I’m seriously considering a new series of blog posts on how to create your own add-in, so that people out there who are considering doing it themselves can see how it is done, in realtime as I create it.

Enjoy,
JP

If you enjoyed this page, share it!