VBA Cell Formatting: NumberFormat, Colors, Borders

Part of the free Module 12: Excel VBA Course · Lesson 11 of 18 · Full Excel course

VBA cell formatting sets the appearance of a range in code: number formats, fill colours, borders, fonts, row height, column width, alignment and wrap text. In this lesson you will learn the property behind each formatting option in Excel VBA, how to use RGB and ColorIndex colours, and how to combine them into a single macro that formats a report header consistently every time.

Table of Contents

Why format with VBA?

Manual formatting is repeated on every new report and drifts over time. A formatting macro applies the house style in one run, works on any size of data and documents the standard in code. Every visual property you can set in the Format Cells dialog has a matching Range property; the examples below cover the ones used daily. Group several settings inside a With sh.Range(...) ... End With block so the range is evaluated once and the code stays readable.

Number formatting

Sub Number_Formatting()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets(1)
    sh.Range("A1:A10").NumberFormat = "0.00"          'two decimals
    sh.Range("B1:B10").NumberFormat = "0.00%"         'percentage
    sh.Range("C1:C10").NumberFormat = "hh:mm AM/PM"   'time
    sh.Range("D1:D10").NumberFormat = "d-mmm-yy"      'date
    sh.Range("E1:E10").NumberFormat = "$#,##0.00"     'currency
End Sub

NumberFormat takes the same format codes you type in the Custom category of the Format Cells dialog; the value is unchanged, only its display.

Cell background colour

Sub Cell_Background_Color()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets(1)
    sh.Range("A1:A10").Interior.Color = vbRed
    sh.Range("B1:B10").Interior.Color = RGB(19, 40, 197)  'any colour
    sh.Range("C1:C10").Interior.ColorIndex = 15           'palette index 1-56
    sh.Range("D1:D10").Interior.ColorIndex = xlNone       'remove fill
End Sub

Interior.Color accepts the eight named constants (vbBlack, vbWhite, vbRed, vbGreen, vbBlue, vbYellow, vbCyan, vbMagenta) or any RGB value; ColorIndex uses the legacy 56-colour palette shown below.

Chart of the 56 VBA ColorIndex palette colours with their index numbers — The 56 ColorIndex palette colours

Borders

Sub Cell_Borders()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.Range("A1:D20").Borders.LineStyle = xlContinuous
    sh.Range("A1:D20").Borders.Weight = xlThin
    sh.Range("A1:D1").Borders(xlEdgeBottom).Weight = xlMedium
End Sub

Borders without an index sets all edges and inside lines; Borders(xlEdgeBottom), xlEdgeTop, xlEdgeLeft, xlEdgeRight, xlInsideHorizontal and xlInsideVertical target one line. LineStyle options include xlContinuous, xlDash, xlDot, xlDouble and xlHairline.

Fonts

Sub Cell_Fonts()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    With sh.Range("A1:D20").Font
        .Color = vbBlue
        .Bold = True
        .Italic = True
        .Size = 15
        .Name = "Arial"
    End With
End Sub

The Font object holds colour, weight, style, size and typeface; Underline and Strikethrough are also available.

Row height, column width and AutoFit

Sub Size_Rows_And_Columns()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.Range("A1:D20").EntireRow.RowHeight = 25
    sh.Range("A1:D20").EntireColumn.ColumnWidth = 15
    sh.Range("A1:D20").EntireColumn.AutoFit
End Sub

RowHeight is measured in points and ColumnWidth in character units; AutoFit sizes columns (or rows) to their contents and is usually the last step of a formatting macro.

Alignment and wrap text

Sub Cell_Alignment()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    With sh.Range("A1:D20")
        .HorizontalAlignment = xlCenter    'xlLeft, xlRight, xlCenterAcrossSelection
        .VerticalAlignment = xlCenter      'xlTop, xlBottom
        .WrapText = True
    End With
End Sub

HorizontalAlignment and VerticalAlignment take the xl constants; WrapText = True breaks long text inside the cell, and xlCenterAcrossSelection is the safe alternative to merging cells.

Complete example: format a report in one run

Sub Format_Report()
    Dim sh As Worksheet
    Dim tbl As Range

    Set sh = ThisWorkbook.Sheets("Sheet1")
    Set tbl = sh.Range("A1").CurrentRegion

    'header row
    With tbl.Rows(1)
        .Interior.Color = RGB(31, 78, 121)
        .Font.Color = vbWhite
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .RowHeight = 22
    End With

    'body
    With tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1)
        .Font.Name = "Calibri"
        .Font.Size = 11
        .VerticalAlignment = xlCenter
        .Columns(4).NumberFormat = "d-mmm-yy"
        .Columns(5).NumberFormat = "#,##0.00"
    End With

    tbl.Borders.LineStyle = xlContinuous
    tbl.Borders.Color = RGB(191, 191, 191)
    tbl.Columns.AutoFit
    sh.Range("A1").Select
End Sub

This runnable macro finds the table with CurrentRegion, styles the header row, sets fonts and number formats for the body, adds light grey borders and autofits, so any export becomes a finished report in one click.

Tips and common mistakes

Named constants beat magic numbers. vbRed and xlCenter are clearer than 255 and -4108.
RGB gives any colour; ColorIndex gives 56. Use RGB for brand colours and ColorIndex only when maintaining old code.
Format ranges, not cells in loops. Formatting a whole range in one statement is far faster than formatting each cell.
Turn off ScreenUpdating (Application.ScreenUpdating = False) for long formatting jobs and switch it back at the end.
Clear formats with Range.ClearFormats before applying a new style to avoid leftovers.

Practice and real-world use

Record a macro while formatting a header by hand, then rewrite it using the properties above in a single With block. Add zebra striping by colouring every second row with a For loop and Mod. Dashboards, invoices and management reports all rely on formatting macros to look identical every period.

Watch the step-by-step video tutorial

Click here to download the practice file.

Related lessons

Frequently asked questions

How do I set a date format in VBA?

Assign a format code to NumberFormat, for example Range(“D2:D100”).NumberFormat = “dd-mmm-yyyy”. The cell keeps its date serial value; only the display changes.

What is the difference between Color and ColorIndex?

Color takes any RGB value (about 16.7 million colours). ColorIndex takes a number from 1 to 56 that points into the workbook’s legacy palette. Use Color for exact brand colours.