Excel Macro Tutorial 2 – Formatting the Columns and Column headers

Excel Macro Tutorial 2 – Formatting the Columns and Column headers

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

[ad name=”Synap sidebar1″]

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

[ad name=”Synap blog wide”]

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

Leave a Reply