📎 This article includes 1 downloadable practice file ↓

📘 Excel VBA Course · Lesson 3 of 15 — see all lessons

Reports look professional when the formatting is consistent — and consistency is exactly what a macro gives you. Here are the formatting commands I use most.

Fonts

With Range("A1:F1").Font
    .Bold = True
    .Size = 12
    .Name = "Calibri"
    .Color = RGB(255, 255, 255)
End With

Fill colour

Range("A1:F1").Interior.Color = RGB(33, 115, 70)   ' Excel green
Range("A2:F2").Interior.Pattern = xlNone              ' remove fill

RGB(r, g, b) gives you any colour; the built-in constants vbYellow, vbRed etc. are handy for quick checks.

Number and date formats

Range("D2:D100").NumberFormat = "#,##0.00"
Range("E2:E100").NumberFormat = "[$₹-4009] #,##0"     ' rupee
Range("F2:F100").NumberFormat = "dd-mmm-yyyy"
Range("G2:G100").NumberFormat = "0.0%"

Borders

With Range("A1").CurrentRegion.Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .Color = RGB(200, 200, 200)
End With

Alignment, wrap and width

With Range("A1:F1")
    .HorizontalAlignment = xlCenter
    .WrapText = True
End With
Columns("A:F").AutoFit

Conditional formatting from VBA

Dim fc As FormatCondition
Set fc = Range("D2:D100").FormatConditions.Add( _
    Type:=xlCellValue, Operator:=xlLess, Formula1:="=0")
fc.Font.Color = vbRed

A reusable “make it a report” macro

Sub FormatReport(ws As Worksheet)
    Dim rng As Range
    Set rng = ws.Range("A1").CurrentRegion
    rng.Font.Name = "Calibri"
    With rng.Rows(1)
        .Font.Bold = True
        .Interior.Color = RGB(33, 115, 70)
        .Font.Color = vbWhite
    End With
    rng.Borders.LineStyle = xlContinuous
    rng.Borders.Color = RGB(210, 210, 210)
    ws.Columns.AutoFit
    ActiveWindow.FreezePanes = False
    ws.Activate: ws.Range("A2").Select: ActiveWindow.FreezePanes = True
End Sub

Call it with FormatReport Worksheets("Sales") after any export and every report looks the same.

See how the macro recorder writes formatting code (and how to clean it) in the Macro Recorder tool.

Try it yourself: step by step

1. Open the practice workbook and look at the plain Data sheet.
2. Run L03_FormatReport: green header, borders and auto-fitted columns in one click.
3. Edit the macro to add Worksheets("Data").Range("G:H").NumberFormat = "#,##0" and run it again.
4. Add a conditional format from code: highlight Amount > 50,000 in red using the FormatConditions example in the article.
5. Record the same steps with the macro recorder and compare the code — or use the online Macro Recorder.