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!