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– BooleanFont.Underline– can bexlUnderlineStyleNone,xlUnderlineStyleSingle, etc.Font.Color– RGB value, e.g.,RGB(255,0,0)for redFont.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
Findor 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
NumberFormatto “@” – if you type a value that looks like a number, Excel may convert it automatically. Always set the format before entering data. - Using
Textproperty instead ofValue–Range.Textreturns the displayed text, which may differ from the underlying value. Use.Valuefor actual content. - Not using
Withblocks – 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.ColorIndexis the same asFont.Color–ColorIndexuses a palette index (1-56), whileColoruses 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
- Excel Template for Weekly Attendance: How to Build One
- Excel Template for Time Sheet: How to Make One
- Excel Template for Tracking Monthly Expenses
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Must Be First Statement on Line?No, the first statement on a line does not have to be a VBA statement at all —…
- Excel VBA COUNTA: Count Nonblank Cells in MacrosIn Excel VBA, count nonblank cells with Application.WorksheetFunction.CountA and pass it a range. For example, Application.WorksheetFunction.CountA(Worksheets("Data").Range("A2:A100")) returns the…
- VBA For Loop Negative: How Step -1 WorksYes. A VBA For loop can count downward by using a negative Step, such as For i =…
- Excel VBA Range Find Exact Match with xlWhole ExplainedTo find a cell whose entire contents match a value in Excel VBA, call Range.Find with LookAt:=xlWhole, and…
- Insert Row in Excel: Shortcut and VBA OptionsIn Excel for Microsoft 365 on Windows, select the row where the new row should go with Shift+Space,…
- Excel Macro Wait 5 Seconds: VBA Code and Options ExplainedTo make an Excel VBA macro pause for about five seconds, call Application.Wait with a time five seconds…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.