Format Cells with VBA: Fonts, Colours, Borders and Number Formats

📎 This article includes 1 downloadable practice file ↓

Reports get judged by how they look before anyone reads a number. Formatting by hand every month is exactly the kind of job macros are for. This lesson covers the formatting you’ll use in real reports and how to apply it quickly to whole ranges.

Fonts

With ws.Range("A1")
    .Value = "Monthly Sales Report"
    .Font.Name = "Calibri"
    .Font.Size = 16
    .Font.Bold = True
    .Font.Color = RGB(196, 83, 31)
End With
ws.Range("A3:F3").Font.Bold = True          ' header row

Fill colour

ws.Range("A3:F3").Interior.Color = RGB(33, 115, 70)     ' Excel green
ws.Range("A3:F3").Font.Color = vbWhite
ws.Range("A4:F100").Interior.Pattern = xlNone             ' clear fills

Borders

With ws.Range("A3").CurrentRegion.Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .Color = RGB(200, 200, 200)
End With
ws.Range("A3:F3").Borders(xlEdgeBottom).Weight = xlMedium

Number formats (including Indian style)

ws.Range("E4:E100").NumberFormat = "₹ #,##0.00"
ws.Range("D4:D100").NumberFormat = "dd-mmm-yyyy"

Alignment, wrap and widths

With ws.Range("A3:F3")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
End With
ws.Columns("A:F").AutoFit
ws.Columns("B").ColumnWidth = 28
ws.Rows(3).RowHeight = 30

Banded rows quickly

Dim r As Long
For r = 4 To lastRow Step 2
    ws.Range(ws.Cells(r, 1), ws.Cells(r, 6)).Interior.Color = RGB(245, 248, 245)
Next r

Even simpler: turn the data into a Table, which bands rows automatically.

Dim lo As ListObject
Set lo = ws.ListObjects.Add(xlSrcRange, ws.Range("A3").CurrentRegion, , xlYes)
lo.Name = "tblSales": lo.TableStyle = "TableStyleMedium2"

Conditional formatting from VBA

With ws.Range("E4:E100").FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=100000")
    .Interior.Color = RGB(255, 235, 156)
    .Font.Bold = True
End With
ws.Range("E4:E100").FormatConditions.AddDatabar

Conditional formats update on their own when values change, unlike colours a macro paints once.

A complete report formatter

Sub FormatReport()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Report")
    Dim rng As Range: Set rng = ws.Range("A3").CurrentRegion
    Application.ScreenUpdating = False
    rng.Font.Name = "Calibri": rng.Font.Size = 11
    With rng.Rows(1)
        .Font.Bold = True: .Interior.Color = RGB(33, 115, 70): .Font.Color = vbWhite
        .HorizontalAlignment = xlCenter
    End With
    rng.Borders.LineStyle = xlContinuous: rng.Borders.Color = RGB(210, 210, 210)
    rng.Columns(5).NumberFormat = "₹ #,##0.00"
    rng.Columns(4).NumberFormat = "dd-mmm-yyyy"
    rng.Columns.AutoFit
    ws.Activate: ws.Range("A4").Select: ActiveWindow.FreezePanes = True
    Application.ScreenUpdating = True
End Sub

Where people go wrong

• Slow formatting loops — format the whole range at once instead of cell by cell.
• Colours look different — ColorIndex depends on the workbook palette; RGB() is exact.
• ₹ shows as ? — the VBA editor can’t store some symbols in code; use ChrW(8377) to build the format string.

Practice

The workbook has an unformatted report and buttons that format it step by step, so you can see what each block of code does. Try changing the colours to your company’s.

Next: decisions — If, ElseIf, And/Or and Select Case.