How to Bold Text using VBA in Excel

The best way to draw a user’s attention to a cell or some text within a cell is to make it look different from the rest of the text.

One way to do this is to make the text bold, thereby highlighting it or stressing its importance.

The ‘Bold’ button in the Excel Home tab is of course everyone’s go-to way of making any text bold. A lot of people also like to use the keyboard shortcut CTRL+B.

However, you might need to make some text bold based on a certain condition, or only at the end of a calculation.

Unfortunately, there’s no Excel function that you can use to make text bold. But it is possible to do this using VBA.

In this tutorial, we will show you how to make text bold using VBA in a number of different use cases.

How to Bold Text using VBA

To make any text bold in VBA, we can use the Font.Bold property of a Range or Cell.

Let’s say you want to bold the text that is in cell A2 of your spreadsheet.

Here’s the code you can use to get this done:

Cells(1,2).Font.Bold=True

You  can also use the Range function to accomplish the same:

 Range("A2").Font.Bold = True

This will also work if you want to bold a whole range of cells, for example:

 Range("A2:A8").Font.Bold = True

This will change cells in the range A1:A8 to bold.

Also read: 100 Useful Excel VBA Macro Codes Examples

Using the Visual Basic Immediate Window to Bold Text in Excel

You can use the Visual Basic developer’s Immediate Window to quickly run one-line VBA codes, such as the ones we discussed in the previous section. 

To run the code in the Immediate Window, simply follow the steps shown below:

1. From the Developer Menu Ribbon, select Visual Basic.

2. Once your VBA window opens, your Immediate Window should be at the bottom. If you cannot see it, then Click View>Immediate Window or press CTRL+G on the keyboard.

3. Paste your line of code in the Immediate Window and press the Return key.

When you get back to your Excel window, you should see the result of the executed code.

So if you used the line:

Range("A2:A8").Font.Bold = True

You should see all the cells in the range A2:A8 have the text converted to bold:

Also read: What is VBA in Excel?

Writing a Visual Basic Module to Bold Text in Excel

If you prefer writing a VBA Module to bold the text instead, then you can enclose the same line of code within a Sub procedure as shown below:

Sub ConvertToBold()
Range("A1:A8").Font.Bold = True
End Sub

Here’s what you have to do in order to use the above procedure:

1. From the Developer Menu Ribbon, select Visual Basic.
2. Once your VBA window opens, Click Insert->Module and paste the above code in the Module window.

3. To run the macro, go back to your Excel window and navigate to Developer->Macros.

4. Select the name of the module, which is ConvertToBold in our example.

5. Click Run.

You should find the cells in the range A2:A8 have the text converted to bold.

Also read: VBA to Find Value in Range in EXCEL

Some Practical Use-cases of the Font.Bold Property

Let us see some practical applications of the Font.Bold property.

How to Bold Selected Cells in Excel Using VBA

Let’s see a small module that can help you bold only the cells that you selected:

'Code by Steve from spreadsheetplanet.com
Sub ConvertSelectedCellsToBold()
Dim Rng As Range
Dim myCell As Object
Set Rng = Selection
For Each myCell In Rng
   myCell.Font.Bold = True
Next
End Sub

To run the code do the following:

1. Select the cells that you want to bold.

2. Copy the above code to your Visual Basic module window.
3. Run the macro.
4. You should find the contents of all your selected cells converted to bold.

Explanation of the Code

In this code, we stored the selected range of cells in the variable Rng.

Then we used a for-loop to traverse over each cell (myCell) in the range, making the contents of each selected cell bold, using the line:

myCell.Font.Bold = True

How to Bold Cells Containing a Specific Text using VBA

Now consider a scenario where you need to highlight all the cells that contain a specific text using VBA.

This can be achieved by the following code:

'Code by Steve from spreadsheetplanet.com
Sub ConvertCellsWithSpecificTextToBold()
Dim Rng As Range
Dim myCell As Object
Dim myUnion As Range
Set Rng = Selection
searchString = InputBox("Please Enter the Search String")
For Each myCell In Rng
If InStr(myCell.Text, searchString) Then
   myCell.Font.Bold = True
End If
Next
End Sub

To run the code do the following:

1. Select the range of cells that you want to work with. 
2. Copy the above code to your Visual Basic module window.
3. Run the macro.
4. You will see a message window that asks you to enter your search string. Type your search string in the input box. In our example, we entered the word “King”.

5. When you press the Return key, you should find all the cells that contain the word “King” highlighted in bold.

Explanation of the Code

In this code, we again stored the selected range of cells in the variable Rng. We used an InputBox to get the user’s search string input and we stored this text in the variable searchString.

We then used a for-loop to traverse over each cell (myCell) in the range and we checked if the cell contained the search string (using the InStr function). If it did, then we made the contents of the cell bold.

How to Find and Bold Specific Text in a Cell using VBA

Now, what if you only want to make the particular search string bold, instead of the entire cell?

For example, you might have a lot of data in your worksheet and you might want to bold only a particular word so that it stands out.

This can be achieved using the following module code:

'Code by Steve from spreadsheetplanet.com
Sub ConvertSpecificTextToBold()
Dim Rng As Range
Dim myCell As Object
Dim myUnion As Range
Set Rng = Selection
searchString = InputBox("Please Enter the Search String")
For Each myCell In Rng
If InStr(myCell.Text, searchString) Then
  myCell.Characters(WorksheetFunction.Find(searchString, myCell.Value), Len(searchString)).Font.Bold = True
End If
Next
End Sub

To run the code do the following:

1. Select the range of cells that you want to work with. 
2. Copy the above code to your Visual Basic module window.
3. Run the macro.
4. You will see a message window that asks you to enter your search string. Type your search string in the input box. In our example, we entered the word “King”.

5. When you press the Return key, you should find only the word “King” highlighted in all your selected cells.

Explanation of the Code

In this code, we stored the selected range of cells in the variable Rng. We used an InputBox to get the user’s search string input and we stored this text in the variable searchString.

We then used a for-loop to traverse over each cell (myCell) in the range and we checked if the cell contained the search string.

If it did, then we used the WorksheetFunction.Find function to find the starting and ending position of the search string in the cell’s contents.

The WorksheetFunction object is used as a container for Excel worksheet functions that can be used in VBA.

So you can use the functions under this object the same way you would use regular functions in Excel. That means, the WorksheetFunction.Find function works the same way as Excel’s FIND function.

The Find function lets you find a search string in another given string and returns the position of the string in the cell’s contents. We used the function as follows:

WorksheetFunction.Find(searchString, myCell.Value)

This means “return the position of searchString in myCell.Value”. This gives the starting position of our search string. To get the ending position of the string we used the length of the search string itself (Len(searchString)).

We then used this information (starting and ending position of the search string) inside the Characters object.

The Characters object represents a range of characters within the cell (myCell). You can use this Characters object to format specific characters within the cell.

So you can set the Font.Bold property of the characters in your search string using the line:

myCell.Characters(WorksheetFunction.Find(searchString, myCell.Value), Len(searchString)).Font.Bold = True

Note: Once you change the font using VBA, you cannot undo the changes using the Undo button.

How to Remove the Bold.Font Setting in VBA

If you want to change the text font back by removing the bold setting, then all you need to do is set the Font.Bold property of the object to False.

In this tutorial, we saw how to bold text using VBA.

We showed you the basic syntax and then showed a few practical use cases where you might need to bold certain text in Excel using VBA.

We hope this was helpful and easy to follow.

Other Excel tutorials you may also like:

• How to Copy Formatting In Excel (4 Easy Ways)
• How to Flash an Excel Cell (Easy Step-by-Step Method)
• How to Auto Format Formulas in Excel (3 Easy Ways)
• How to Reverse a Text String in Excel (Using Formula & VBA)
• Highlight Cell If Value Exists in Another Column in Excel
• How to Set the Default Font in Excel (Windows and Mac)
• How to Add Bullet Points in Excel
• How to Rotate Text in Excel?