Excel VBA Code to Format Cells: A Practical Guide

VBA & macros · Updated November 22, 2026

The fastest way to format cells with Excel VBA is to use the .Font, .Interior, and .Borders properties of a Range object, for example: Range("A1").Font.Bold = True. This article explains the core syntax, common formatting operations, and practical examples you can adapt to your own workbooks.

Why Use VBA for Cell Formatting?

Manual formatting works for one-off tasks, but VBA shines when you need to apply the same formatting repeatedly, across many sheets, or based on cell values. Instead of clicking through menus each time, a macro can format an entire report in seconds. VBA also lets you create dynamic formatting—for instance, highlighting every row where a sales figure exceeds a target—without using conditional formatting rules.

Core Objects and Properties

In VBA, every cell or range is a Range object. Formatting is controlled by properties that return child objects:

  • Range.Font – controls font name, size, bold, italic, color, etc.
  • Range.Interior – controls background color and pattern.
  • Range.Borders – controls border style, weight, and color.
  • Range.NumberFormat – sets the display format for numbers (e.g., currency, date).
  • Range.HorizontalAlignment and Range.VerticalAlignment – set alignment.

Here’s a simple example that formats cell A1 with bold, red text on a yellow background:

Sub FormatExample()
    With Range("A1")
        .Font.Bold = True
        .Font.Color = RGB(255, 0, 0)
        .Interior.Color = RGB(255, 255, 0)
    End With
End Sub

Note: RGB(red, green, blue) takes values from 0 to 255. Save a copy of your workbook before running any macro, because formatting changes cannot be undone with Ctrl+Z.

Common Formatting Operations

Below are typical formatting tasks with their VBA code. Each example assumes you have a worksheet with data in the specified range.

Font Formatting

To make a range bold, italic, or change the font size:

With Range("A1:A10")
    .Font.Name = "Calibri"
    .Font.Size = 12
    .Font.Bold = True
    .Font.Italic = False
End With

To change font color, use .Font.Color with an RGB value or a color constant like vbRed.

Background Color

Fill a range with a light blue background:

Range("B2:B20").Interior.Color = RGB(173, 216, 230)

To remove the fill, set .Interior.ColorIndex = xlNone.

Borders

Add a thin border around a range:

With Range("C1:E10")
    .Borders.LineStyle = xlContinuous
    .Borders.Weight = xlThin
    .Borders.Color = RGB(0, 0, 0)
End With

To apply borders only to specific edges, use .Borders(xlEdgeLeft), xlEdgeTop, etc.

Number Formats

Set a range to display numbers as currency with two decimals:

Range("D1:D10").NumberFormat = "$#,##0.00"

For dates, use "mm/dd/yyyy" or "yyyy-mm-dd".

Alignment and Wrap Text

Center text horizontally and vertically, and enable wrapping:

With Range("A1:D1")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
End With

Formatting Based on Cell Values

A common need is to format cells conditionally based on their content. For example, highlight all cells in column A that contain the word “Overdue”:

Sub HighlightOverdue()
    Dim cell As Range
    For Each cell In Range("A1:A100")
        If cell.Value = "Overdue" Then
            cell.Interior.Color = RGB(255, 0, 0)
        End If
    Next cell
End Sub

This loops through each cell and applies formatting. For numeric comparisons, use If cell.Value > 100 Then.

Using With Blocks for Efficiency

When applying multiple formatting changes to the same range, use a With block to avoid repeating the range reference. This makes code faster and easier to read:

Sub FormatHeader()
    With Range("A1:F1")
        .Font.Bold = True
        .Font.Color = vbWhite
        .Interior.Color = RGB(0, 102, 204)
        .HorizontalAlignment = xlCenter
        .Borders.LineStyle = xlContinuous
    End With
End Sub

Note: vbWhite is a built-in constant for white (RGB 255,255,255).

Common Errors and How to Avoid Them

Beginners often encounter these issues:

  • Wrong range reference – Always qualify ranges with a worksheet, e.g., Worksheets("Sheet1").Range("A1"), especially when multiple sheets are active.
  • Using Select unnecessarily – Avoid Select and Activate; they slow down code and cause errors. Work directly with the range.
  • Forgetting to clear previous formatting – When reusing a macro, old formatting may remain. Use .ClearFormats on the range before applying new formatting.
  • ColorIndex vs Color – ColorIndex uses a limited palette (1–56), while Color accepts full RGB. Mixing them can cause unexpected results.

Alternatives to VBA for Formatting

If you only need conditional formatting based on rules, Excel’s built-in Conditional Formatting (Home > Conditional Formatting) is simpler and doesn’t require macros. For one-time formatting, manual formatting is fine. VBA is best when you need to automate repetitive tasks or apply complex logic that conditional formatting can’t handle. Also, remember that macros must be saved in a macro-enabled workbook (.xlsm) and may be blocked by security settings—enable them only for trusted files.

Frequently Asked Questions

How do I format an entire row based on a cell value?

Use a loop to check a key cell in each row, then format the entire row range. For example, if column A holds a status, you can write: If Range("A" & i).Value = "Yes" Then Range("A" & i & ":F" & i).Interior.Color = vbGreen.

Can I use VBA to copy formatting from one cell to another?

Yes, use the Copy method with PasteSpecial to copy only formatting: Range("A1").Copy: Range("B1").PasteSpecial Paste:=xlPasteFormats. Alternatively, you can manually copy each property.

How do I remove all formatting from a range?

Use Range("A1:C10").ClearFormats. This resets font, fill, borders, and number format to default, but does not clear cell contents.

Does VBA formatting work on Excel for Mac?

Yes, the same properties exist on Mac, but some color constants may differ. Use RGB values for consistency. Keyboard shortcuts differ (e.g., Option+F11 opens the VBA editor on Mac), but the code itself is identical.

Why does my macro not run?

Common reasons: macros are disabled in Trust Center, the workbook is saved as .xlsx (not .xlsm), or the code contains a syntax error. Check the VBA editor for error messages and ensure the macro is in a standard module, not a worksheet’s code page.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.