We need to autosize the columns to fit the data and then change the background color of the column headers.

How to autosize columns with and Excel macro:

Autosizing the columns with a macro is very easy.  This is what the recorder recorded:

Sub ColumnsAutoSize()
  '

' ColumnsAutoSize Macro

'

    Columns("A:H").Select

    Selection.Columns.AutoFit

End Sub

Very simple, but we can still clean it up a bit. There is no reason to select anything in a macro usually. Anytime you see a “.Select” followed by a “Selection.”, you can simply delete the selection stuff and combine it to one line:

Sub ColumnsAutoSize()
  '

' ColumnsAutoSize Macro

'

    Columns("A:H").Columns.AutoFit

End Sub

 

How to change background color of a cell in an Excel Macro:

That’s it for autosizing. Now adding a light gray background to the headers is a very simple matter:

Sub ColumnsAutoSize()
  '

' ColumnsAutoSize Macro

'

    Columns("A:H").Columns.AutoFit

    Range("A1:H1").Interior.ColorIndex = 15

End Sub

They also want us to format the E and G columns as currency. The recorder produces:

    Range("E:E,G:G").Select
    Selection.NumberFormat = "$#,##0.00"

We can simplify this and add it to the ColumnsAutoSize macro. Now that the macro does more than just autosizing, I will rename it too:

Sub ColumnsFormat()
  '

' ColumnsAutoSize Macro

'

    Columns("A:H").Columns.AutoFit

    Range("A1:H1").Interior.ColorIndex = 15

    Range("E:E,G:G").NumberFormat = "$#,##0.00"

End Sub

In the next tutorial I will show you how to remove junk headers and footers from the report

Excel Macro Tutorial 1 – Open a text file

Excel Macro Tutorial 2 – Formatting the Columns and Column headers

Excel Macro Tutorial 3 – Remove header rows and delete junk loop

Excel Macro Tutorial 4 – Delete non-chargeable tests and sort

Excel Macro Tutorial 5 – Delete cancelled tests, flag credited tests

Excel Macro Tutorial 6 – Add macro buttons to your toolbar