To format a cell’s text as bold in Excel VBA, use the Font.Bold property: Range("A1").Font.Bold = True. This single line of code sets the entire cell’s font to bold; for partial bold within a cell, access the Characters object instead.
Why Use VBA for Bold Formatting?
Excel’s built-in bold button (Home > Font > Bold, or Ctrl+B) works fine for manual edits, but VBA becomes essential when you need to automate formatting across many cells, apply conditional logic, or format cells based on data values. For example, you might want to bold every cell in a column where the value exceeds a threshold, or highlight headers before printing a report. VBA runs the same action repeatedly without manual effort, saving time and reducing errors.
Basic Syntax and Examples
The core property is Font.Bold, which accepts True (bold), False (not bold), or Null (inherits from the style). Here are common patterns:
- Single cell:
Range("A1").Font.Bold = True - Multiple cells:
Range("A1:C10").Font.Bold = True - Entire row or column:
Rows(1).Font.Bold = TrueorColumns("B").Font.Bold = True - Using a variable:
Dim rng As Range: Set rng = Range("A1"): rng.Font.Bold = True
You can also toggle bold off by setting False, or check if a cell is bold with If Range("A1").Font.Bold = True Then.
Bold Only Part of a Cell’s Text
Sometimes you need only a portion of a cell’s text to be bold, such as a label followed by a value. Use the Characters property with a start position and length:
With Range("A1")
.Value = "Total: 100"
.Characters(1, 6).Font.Bold = True ' bolds "Total:"
End With
Here, Characters(start, length) refers to the first 6 characters (“Total:”). The rest remains normal. This works only if the cell contains text, not a number or formula result.
Formatting with Conditions (If-Then)
Often you want to bold cells that meet a condition. Combine Font.Bold with an If statement or a loop:
Dim cell As Range
For Each cell In Range("B2:B10")
If cell.Value > 100 Then
cell.Font.Bold = True
End If
Next cell
This checks each cell in B2:B10 and bolds those with values greater than 100. For more complex logic, you can use Select Case or And/Or operators.
Common Problems and Solutions
- Bold not applying to the whole cell: If you used
Characters, only that part is bold. To reset, setFont.Bold = Falsefirst, then reapply. - Error “Object required”: Ensure you reference a valid range. If using a sheet name, qualify it:
Worksheets("Sheet1").Range("A1").Font.Bold = True. - Macro doesn’t run: Check that macros are enabled (File > Options > Trust Center > Macro Settings). Save as .xlsm to keep the macro.
- Bold not visible on merged cells: Merged cells can behave oddly; apply bold to the entire merged range, not just the top-left cell.
Performance Tips for Large Ranges
Applying bold to thousands of cells individually can be slow. To speed up, set the Font.Bold property on the entire range at once, or use Application.ScreenUpdating = False at the start and True at the end to prevent screen refreshes. Example:
Application.ScreenUpdating = False
Range("A1:D10000").Font.Bold = True
Application.ScreenUpdating = True
This processes the whole range in one operation, which is much faster than looping through each cell.
Alternatives to VBA for Bold Formatting
If you don’t want to use VBA, consider these built-in options:
- Conditional Formatting: Home > Conditional Formatting > New Rule > Use a formula. For example, to bold cells in A1:A10 when value > 100, use formula
=A1>100and set the font to bold. This updates automatically when values change. - Format Painter: Manual but quick for copying bold formatting from one cell to another.
- Table Styles: If you convert data to a table (Ctrl+T), you can bold header rows with a style.
Conditional Formatting is often preferable for dynamic data because it doesn’t require running a macro.
Frequently Asked Questions
How do I bold an entire row based on a cell value?
Use a loop with EntireRow: If Range("A1").Value = "Yes" Then Rows(1).Font.Bold = True. For multiple rows, loop through a range and check the condition.
Can I bold text in a cell without affecting other cells?
Yes, target the specific cell or range. If you use Range("A1").Font.Bold = True, only A1 is affected. To bold part of the text, use Characters as shown above.
Why does my bold macro stop working after I close and reopen the file?
Macros are not stored in .xlsx files. Save the workbook as .xlsm (macro-enabled) to keep the VBA code. Also ensure macros are enabled when opening.
Is there a keyboard shortcut to run a bold macro?
You can assign a shortcut key when recording or creating the macro: in the Macro dialog, click Options and enter a letter. For example, Ctrl+Shift+B. On Mac, use Option+Cmd+B.
What if I want to bold only numbers in a cell?
VBA cannot differentiate numbers from text within a cell using Characters alone. You would need to analyze the cell’s value and use InStr to find number positions, then apply bold to those character ranges. This is complex; consider using a custom function or splitting data into separate cells.
Remember to save a copy of your workbook before running any macro, as VBA actions cannot be undone with Ctrl+Z. Test on a small range first to verify the logic.
Related
- Excel Template Job Schedule: How to Build One
- Excel Template for Weekly Employee Schedule: How to Build One
- Vacation Budget Excel Template: How to Plan & Track
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Not Responding During VBA? Causes and FixesIf Excel says Not Responding while VBA runs, it often means the macro is occupying Excel’s interface thread—not…
- Excel VBA: Find a Character’s Position in a StringIn Excel VBA, use InStr to find a character’s position in a string: it returns the character’s one-based…
- Excel Macro MsgBox Syntax: Buttons, Results, and ExamplesIn Excel VBA, the basic MsgBox syntax is MsgBox(prompt, [buttons], [title], [helpfile], [context]). For a simple message, call…
- Excel VBA: Find the Last Used Row in Any WorksheetIn Excel VBA, use the worksheet’s Find method to locate the last cell containing content anywhere on the…
- Excel VBA Copy and Paste a Column: Examples and TipsTo copy a column with Excel VBA, use sourceWs.Columns("B").Copy Destination:=targetWs.Columns("D"), replacing the worksheet variables and column letters with…
- VBA Hide UserForm: Methods, Code, and When to Use ThemTo hide an Excel VBA UserForm without unloading it, call Me.Hide from code inside the form. The form…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.