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.

plain text version

VBA:

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

plain text version

VBA:

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: