To bold a cell in Excel VBA, use the Font.Bold property: Range("A1").Font.Bold = True. This single line of code toggles bold formatting on for the specified cell; set it to False to remove bold. Below is everything you need to know about applying, removing, and troubleshooting bold formatting in VBA, including how to work with entire ranges, conditional bold, and performance tips.
Why Use VBA for Bold Formatting?
Excel’s built-in bold button (Home > Font > Bold, or Ctrl+B) works fine for static formatting. But VBA becomes essential when you need to:
- Format hundreds of cells based on a value or formula (e.g., bold all rows where sales exceed $10,000).
- Apply bold as part of a larger automated report or data-cleaning macro.
- Toggle bold on/off repeatedly without manual clicks.
- Ensure consistency across multiple workbooks or sheets.
VBA gives you programmatic control, so you can combine bold with font size, color, or borders in one routine.
Basic Syntax and Variations
The core property is Font.Bold, which accepts True, False, or Null (to inherit from the cell’s style). Here are common usage patterns:
' Bold a single cell
Range("A1").Font.Bold = True
' Bold a range of cells
Range("A1:C10").Font.Bold = True
' Bold an entire row or column
Rows(3).Font.Bold = True
Columns("B").Font.Bold = True
' Remove bold
Range("A1").Font.Bold = False
' Check if a cell is bold (returns True/False)
If Range("A1").Font.Bold = True Then MsgBox "It's bold"
You can also use Selection.Font.Bold = True if you want to format whatever the user has selected, but it’s better practice to reference specific ranges to avoid unintended changes.
Bold Based on Cell Values (Conditional Bold)
A common task is to bold cells that meet a condition. For example, bold all cells in column B where the value is greater than 100. You can loop through the range:
Dim cell As Range
For Each cell In Range("B1:B20")
If cell.Value > 100 Then
cell.Font.Bold = True
Else
cell.Font.Bold = False
End If
Next cell
For larger datasets (thousands of rows), looping can be slow. A faster approach uses the AutoFilter method to filter and then format visible cells only:
' Assumes data in A1:B100 with headers in row 1
Range("A1:B100").AutoFilter Field:=2, Criteria1:=">100"
Range("B2:B100").SpecialCells(xlCellTypeVisible).Font.Bold = True
Range("A1:B100").AutoFilter ' Turn off filter
This applies bold to all visible cells in column B that pass the filter—much faster than a loop.
Bold in a Table or ListObject
If your data is in an Excel Table (created via Insert > Table), you can reference the table’s columns by name instead of using A1-style references. For instance, to bold the entire “Amount” column:
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
tbl.ListColumns("Amount").DataBodyRange.Font.Bold = True
This works even if the table moves or resizes, making it more robust than fixed ranges.
Common Problems and How to Fix Them
Bold Doesn’t Apply to the Entire Cell
If you only want to bold part of a cell’s text (e.g., the first word), you need to use the Characters property. For example, to bold the first 5 characters in A1:
Range("A1").Characters(1, 5).Font.Bold = True
This works only for text cells, not for numbers or dates.
Bold Applies to the Wrong Cells
Often this happens because you’re using Selection or ActiveCell without realizing the active cell changes. Always use explicit references like Worksheets("Sheet1").Range("A1") to avoid confusion.
Bold Toggles Instead of Setting
If you use Font.Bold = True on a cell that is already bold, it stays bold. But if you use Font.Bold = Not Font.Bold, it toggles. That’s intentional. If you want to ensure a cell is NOT bold, set it to False explicitly.
Macro Runs Slowly on Large Ranges
Looping cell-by-cell is slow. Use Range.AutoFilter or Range.SpecialCells to format only relevant cells. Also, turn off screen updating at the start of your macro:
Application.ScreenUpdating = False
' ... your formatting code ...
Application.ScreenUpdating = True
This speeds up execution significantly.
Keyboard Shortcuts and Mac Differences
In Excel for Windows, the shortcut for bold is Ctrl+B. On a Mac, it’s Cmd+B. These shortcuts work in the Excel interface, not in the VBA editor. In VBA, you don’t use shortcuts; you write code. However, you can assign a macro to a button or a keyboard shortcut (via the Macro dialog) to trigger your bold formatting routine.
When writing VBA that runs on both Windows and Mac, the code is identical for Font.Bold—no platform-specific syntax is needed. The only difference is in how you trigger the macro (e.g., shortcut keys in the Mac version of Excel may use Cmd instead of Ctrl).
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 B2:B100 when the value is >100, set the formula
=B2>100and click Format > Font > Bold. This updates automatically when values change. - Excel Table Styles: You can apply a table style that bolds the header row, but not individual data cells based on values.
- Find & Replace: Use Ctrl+H to replace a format (e.g., find all cells with a certain value and apply bold via the Format button in the Replace dialog). This works for static changes.
Conditional Formatting is often the best choice because it’s dynamic and doesn’t require macros. But VBA gives you more flexibility, such as bolding based on multiple conditions or combining with other actions like sending an email.
Frequently Asked Questions
How do I bold an entire row if a cell in that row meets a condition?
Loop through each row and check the condition, then set the entire row’s font bold. For example, to bold rows where column A equals “Yes”:
Dim r As Long
For r = 1 To 100
If Range("A" & r).Value = "Yes" Then
Rows(r).Font.Bold = True
End If
Next r
This bolds every cell in that row, not just the cell with the condition.
Can I bold only part of a cell’s text in VBA?
Yes. Use the Characters(start, length) property. For example, to bold the first 3 characters in A1: Range("A1").Characters(1, 3).Font.Bold = True. This works only for text strings, not for numeric values.
Why does my macro not bold the cell when I run it?
Check these common causes: (1) The cell is locked or the sheet is protected—unprotect the sheet first. (2) You’re referencing the wrong worksheet—use Worksheets("SheetName") explicitly. (3) The cell contains a formula that returns a value, but bold should still work—make sure you’re not accidentally resetting the font later in the code. (4) You might have Application.ScreenUpdating = False without turning it back on—that doesn’t prevent formatting, but it might make you think nothing happened.
Is there a way to bold cells without using a loop?
Yes, use Range.AutoFilter or Range.SpecialCells to target cells that meet a condition. For example, to bold all cells in column B that are greater than 100 without looping, you can filter and then format visible cells as shown earlier. Alternatively, use Range("B1:B100").SpecialCells(xlCellTypeConstants, xlNumbers) to get numeric cells and then apply bold to all of them at once.
Does Font.Bold = True work on merged cells?
Yes, it works on merged cells. When you set the font of a merged range, the entire merged area gets bold. However, if you try to set the font of only one cell in a merged range, Excel may throw an error. Always reference the entire merged range (e.g., Range("A1:C1") if that’s the merged area).
Can I bold cells based on a formula result?
Absolutely. In VBA, evaluate the formula result. For example, to bold cells where the formula in column C returns TRUE: If Range("C1").Value = True Then Range("A1").Font.Bold = True. Or use Conditional Formatting with a formula rule, which is often simpler and doesn’t require VBA.
Remember to always save a copy of your workbook before running any macro, because VBA changes cannot be undone with Ctrl+Z. Test on a backup file first.
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 VBA Cell Interior Color: RGB, Hex, and Code ExamplesIn Excel VBA, a cell’s fill color is read or set with Range("A1").Interior.Color, which uses a numeric color…
- Excel VBA InputBox String: Return Values and Type HandlingIn Excel VBA, the built-in InputBox function returns the user’s response as a String. One important catch is…
- VBA Code for UserForm Data Entry: Build a Reliable FormTo enter records through an Excel VBA UserForm, create text boxes and a combo box on the form,…
- Excel UserForm Label: Setup, Properties, and VBA ExamplesAn Excel UserForm label is a control that displays text on a VBA form, such as an instruction,…
- VBA Array Clear: How to Erase, Reset, and Reuse ArraysTo clear a VBA array, use Erase arrayName. For a dynamic array, Erase releases its allocated storage; for…
- Excel Macro Code: How to Write, Run, and Use It SafelyExcel macro code is usually written in Visual Basic for Applications (VBA), a language that automates tasks in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.