Excel VBA Format Text: How to Change Cell Formatting

VBA & macros · Updated November 22, 2026

To format text in Excel VBA, you use the Range.Font and Range.NumberFormat properties. For example, Range("A1").Font.Bold = True makes the text bold, and Range("A1").NumberFormat = "@" forces the cell to treat its content as text. This article explains the key properties, common formatting tasks, and how to avoid frequent mistakes.

Why Format Text with VBA?

Excel’s built-in formatting options cover most everyday needs, but VBA becomes essential when you need to format many cells at once, apply formatting based on conditions, or automate reports that require consistent styling. For instance, you might want to highlight all negative numbers in red, or format a column of phone numbers as text to preserve leading zeros. Doing this manually for hundreds of rows is tedious and error-prone; VBA makes it repeatable and fast.

Core Properties for Text Formatting

The Font object is the primary way to change text appearance. Key properties include:

  • Font.Bold – Boolean (True/False)
  • Font.Italic – Boolean
  • Font.Underline – can be xlUnderlineStyleNone, xlUnderlineStyleSingle, etc.
  • Font.Color – RGB value, e.g., RGB(255,0,0) for red
  • Font.Size – point size (e.g., 11, 12)
  • Font.Name – font type, e.g., “Calibri”

To set multiple properties at once, you can use a With block:

With Range("A1").Font
    .Bold = True
    .Italic = True
    .Color = RGB(0, 100, 200)
    .Size = 14
End With

Remember to save a copy of your workbook before running any macro, as changes cannot be undone with Ctrl+Z.

Number Format vs. Text Format

Often “formatting text” means ensuring a cell displays a value as text, not changing the font. Use the NumberFormat property. Setting NumberFormat = "@" forces text format. For example, to treat a column of numeric IDs as text:

Range("B2:B100").NumberFormat = "@"

This prevents Excel from stripping leading zeros or converting long numbers to scientific notation. To apply a specific format like a date or currency, you assign a format string, e.g., NumberFormat = "mm/dd/yyyy" or "$#,##0.00".

Formatting Specific Text Within a Cell

Sometimes you need to format only part of a cell’s content (e.g., make the first word bold). Use the Characters property:

With Range("A1")
    .Characters(1, 5).Font.Bold = True
    .Characters(7, 3).Font.Italic = True
End With

This works on a single cell. For multiple cells, you’d loop through each. Note that Characters(start, length) uses 1-based indexing.

Common Formatting Tasks and Examples

Here are typical scenarios with code snippets:

  • Format a header row: Bold, larger font, background color.
    With Range("A1:D1")
        .Font.Bold = True
        .Font.Size = 12
        .Interior.Color = RGB(200, 200, 200)
    End With
  • Highlight cells containing specific text: Use Find or loop.
    For Each cell In Range("A1:A100")
        If InStr(cell.Value, "urgent") > 0 Then
            cell.Font.Color = RGB(255, 0, 0)
        End If
    Next cell
  • Apply text format to a whole column:
    Columns("C").NumberFormat = "@"

Formatting Cells Based on Conditions

VBA can replicate and extend conditional formatting. For example, to bold all cells in a range that are greater than 100:

For Each cell In Range("B2:B50")
    If cell.Value > 100 Then
        cell.Font.Bold = True
    End If
Next cell

You can also use the FormatConditions collection to add conditional formatting rules via VBA, but that is more complex. For simple cases, looping is easier.

Common Problems and How to Avoid Them

  • Forgetting to set NumberFormat to “@” – if you type a value that looks like a number, Excel may convert it automatically. Always set the format before entering data.
  • Using Text property instead of Value – Range.Text returns the displayed text, which may differ from the underlying value. Use .Value for actual content.
  • Not using With blocks – this makes code slower and harder to read.
  • Running macros without saving – always save a backup copy, as VBA changes cannot be undone.
  • Assuming Font.ColorIndex is the same as Font.Color – ColorIndex uses a palette index (1-56), while Color uses RGB. Prefer RGB for precision.

Alternatives to VBA for Formatting

If you only need to format text occasionally, consider using Excel’s built-in features: the Home tab has font formatting buttons, and the Number group has a drop-down for text format. For conditional formatting, use Home > Conditional Formatting > Highlight Cells Rules. These are simpler and don’t require coding. However, if you need to apply the same formatting to many workbooks or on a schedule, VBA is the better choice.

Frequently Asked Questions

How do I format a cell as text in Excel VBA?

Set the NumberFormat property to “@” for the range. For example: Range("A1").NumberFormat = "@". This ensures that any value entered is treated as text, preserving leading zeros and preventing number conversion.

Can I change font color in VBA?

Yes, use Range.Font.Color = RGB(red, green, blue) or Range.Font.ColorIndex = n. For example, Range("A1").Font.Color = RGB(255, 0, 0) makes it red.

How do I bold a specific part of a cell’s text?

Use the Characters property. For instance, Range("A1").Characters(1, 5).Font.Bold = True bolds the first five characters. This works only on a single cell.

Why is my VBA formatting not working?

Common reasons: you’re modifying the wrong range, the cell contains a formula (formatting may be overridden), or you forgot to set NumberFormat to “@” before entering data. Also check that the macro is enabled and you’re running the correct procedure.

Can I use VBA to format text in multiple worksheets?

Yes, loop through worksheets using For Each ws In ThisWorkbook.Worksheets and apply formatting to each. For example, to format A1 in every sheet, use ws.Range("A1").Font.Bold = True inside the loop.

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.