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!