Here’s a neat piece of code I use whenever I needed to apply a macro that formats headers on an Excel workbook. I always come across this step whenever I write a datatable to an Excel file. Datatables are being copied as plain text so you have to format it to make it look more presentable.
The goal is to programmatically turn this unformatted workbook…

to a formatted one which is very much readable and ready for reporting:

Here you go:
Private Sub FormatHeaders()
Dim ws as Worksheet
For Each ws in Worksheets
ws.Activate
Cells.EntireColumn.Autofit
Cells.EntireRow.Autofit
Rows("1:1").Font.Bold = True
Range(Range("A1"),Range("A1").End(xlToRight)).Interior.Color = RGB(173, 216, 230)
Next
Sheets(1).Select
End Sub
What this macro does is loop through all the sheets in the workbook and do the following:
- Autofit all columns
- Autofit all rows
- Bold the headers
- Add background color to the headers
Then it will focus back on the first sheet.
Now it’s easy to read and ready to sent out. Remember to setup MS Excel’s trust settings if you’re going to run an external macro.
Hope this helps!
Leave a Reply