Changing font styles while working with Microsoft Excel is one of the familiar tasks. We perform this in Excel day-to-day. Changing the font style to bold is one of the simplest tasks. But whenever you have to bold text within a string in Excel, the VBA code will do that for you with ease. Microsoft Excel’s Visual Basic for Application can solve these types of problems.
In this tutorial, you will learn to make a text bold in a string using Excel VBA along with other related problems. It will be on point with suitable examples and proper illustrations. So, stay with us.
Download Practice Workbook
5 VBA Codes to Bold Text in a String in Excel
1. Bold the Whole Text String with Excel VBA
To bold a Text using VBA, we apply Font.Bold property.
The Generic Code:
Range.Font.Bold = True
Have a look at the following dataset:
Here, we have some text strings in the column. We will make them bold using the VBA code. Now, press B on your keyboard to open the Visual Basic Editor window. Next. click on Insert > Module.
Now, to bold Cell B5, type the following code:
Sub bold_string()
Cells(5, 2).Font.Bold = True
End Sub
Here, we used the Cells method to select Cell B5.
Now, run the code and you will see the following:
As you can see, we made the font bold to that text string. Now, you can use the Range method to make it bold. To do that, type the following:
Sub bold_range_string()
Range("B5").Font.Bold = True
End Sub
It will give you the same result:
To make the Range of cells B5:B9 to bold, type the following code:
Sub bold_column()
Range("B5:B9").Font.Bold = True
End Sub
As you can see, we successfully used the VBA code to bold a text string in Excel.
Read More: How to Format Text in Excel Cell (10 Approaches)
2. VBA to Bold Partial Text in a String in Excel
Now, you can bold a specific text in a string with Excel VBA. It is an essential task to analyze your dataset. Sometimes, we have to identify some text from the dataset for research purposes. This method will come in handy in a lot of situations in the future.
Have a look at the screenshot:
Here, we the text strings in the column. Now, our goal is to search the string for the text “Computer” and after that bold it in the entire column.
We can solve this by using the following VBA code:
Sub bold_text_in_string()
Dim r As Range
Dim cell As Range
Set r = Range("B5:B10")
text_value = InputBox("Please Enter Text You Want to Search and Bold")
For Each cell In r
If InStr(cell.Text, text_value) Then
cell.Characters(WorksheetFunction.Find(text_value, cell.Value), Len(text_value)).Font.Bold = True
End If
Next
End Sub
👉
Set r = Range(“B5:B10”): By this line of code I took the range of cells of our dataset.
👉
text_value = InputBox(“Please Enter Text You Want to Search and Bold”): By this lone of VBA code, I take the user input and store it into text_value.
👉
For Each cell In r: I ran a loop to our dataset.
👉
If InStr(cell.Text, text_value)
cell.Characters(WorksheetFunction.Find(text_value, cell.Value), Len(text_value)).Font.Bold = True: This line checks whether we have the specific text in the cell or not. If it finds the matching text in the string, it finds those characters and makes them bold.
To search the text in the string we used the InStr function of VBA. In VBA, it is one kind of String function.
After running the VBA code, you will see the following output:
Type the text you want to search and bold in the String. We want to bold the text “Computer”. After that, click on OK.
As you can see, our VBA code successfully found and made font bold of the text “Computer” from each string in Excel.
Read More: Excel VBA: Format Cell as Text (3 Methods)
3. Bold Selected Cells of Text or String in Excel
You can bold selected cells of the text and string using the following VBA:
Sub bold_selected_cell()
Dim r As Range
Set r = Selection
r.Font.Bold = True
End Sub
Now, select the cells you want to bold:
After that, press Alt+F8 on your keyboard to open a macro dialog box.
Then, select bold_selected_cells and click on Run.
As you can see, our Excel VBA code bolds the selected cells of text or string in the dataset.
Read More: Excel VBA: Change Font Color for Part of Text (3 Methods)
Similar Readings
- How to Convert Text to Time Format with AM/PM in Excel (3 Methods)
- How to Add Leading Zeros in Excel Text Format (10 Ways)
- [Fixed!] Unable to Change Font Color in Excel (3 Solutions)
- How to Capitalize First Letter of Each Word in Excel (4 Ways)
- How to Change Lowercase to Uppercase in Excel (6 Methods)
4. Bold Cells Having Specific Text Using VBA
Now, previously I showed you an example to bold a text in a string using the VBA. In this example, I will show you to bold an entire string having that specific text using the VBA code.
To demonstrate this, I am using the previous dataset:
Here, our goal is to make the entire string bold having the text “Computer” using the VBA.
To do this, type the following VBA code:
Sub bold_entire_string()
Dim r As Range
Dim cell As Range
Set r = Range("B5:B10")
text_value = InputBox("Please Enter Your Desired Text")
For Each cell In r
If InStr(cell.Text, text_value) Then
cell.Font.Bold = True
End If
Next
End Sub
👉
Set r = Range(“B5:B10”): By this line of code I took the range of cells of our dataset.
👉
text_value = InputBox(“Please Enter Your Desired Text”): By this lone of VBA code, I take the user input and store in into text_value.
👉
For Each cell In r: I ran a loop to our dataset.
👉
If InStr(cell.Text, text_value) Then
cell.Font.Bold = True: This line checks whether we have the specific text in the cell or not. If it finds the matching text in the string, it makes the entire string bold.
To search the text in the string we used the InStr function of VBA. In VBA, it is one kind of String function.
After running the VBA code, you will see the following output:
Type the text you want to search and bold that entire String. We want to bold the string having the text “Computer”. After that, click on OK.
As you can see, we have successfully used the VBA codes to bold the cells of a string having the specific text in Excel.
Read More: How to Format Text to Capitalize First Letter in Excel (10 Ways)
5. Bold Cells Based on IF Condition Using VBA
All the examples I showed you before had the IF conditions in them. We basically made a text or string bold based on specific conditions. Here, I am giving you another example to bold cells based on IF Conditions using Excel VBA.
Have look at the following dataset:
Here we have a dataset of students’ marks. The passing marks in 32. Now, our goal is to determine whether is the student is passed or failed. If someone passes, I will remark them with the text “Passed”. Otherwise, I will remark the with the text “Failed”. Again, I will also bold the cells containing the text “Failed”.
Now, type the following code to perform this:
Sub bold_condition()
Dim cell, rng As Range
Set rng = Range("C5:C10")
For Each cell In rng
If cell.Value > 32 Then
cell.Offset(0, 1).Value = "Passed"
Else
cell.Offset(0, 1).Value = "Failed"
cell.Offset(0, 1).Font.Bold = True
End If
Next cell
End Sub
👉
Set rng = Range(“C5:C10”): With this code, we set the range of marks.
👉
If cell.Value > 32 Then: We are checking whether the cell value is greater than 32 or not.
👉
cell.Offset(0, 1).Value = “Passed”: If the cell value is greater than 32, it will set the adjacent cell value to “Passed”.
👉
cell.Offset(0, 1).Value = “Failed”: If the cell value is less than 32, it will set the adjacent cell value to “Failed”.
To bold the text, I used the following:
👉
cell.Offset(0, 1).Font.Bold = True: After setting the adjacent cell value to “Failed”, it makes the font bold.
Now, run the code and you will see the following:
As you can see, our VBA codes worked exactly to bold cells based on IF conditions in Excel.
Related Content: Text Alignment with VBA Command Button in Excel (5 Methods)
Bold Entire Row Using VBA in Excel
We have learned how to bold text in a string with Excel VBA codes in the previous sections. Now we will learn how to bold an entire row using VBA codes.
To make an entire row you can follow two methods:
First, you can use the row number to bold the entire row by using the following code-
Sub bold_entire_row()
Rows(7).Font.Bold = True
End Sub
It will bold the entire row 7.
Or you can select a range of row 7 and make the entire row bold using the EntireRow method:
Sub bold_entire_row2()
Range("B7").EntireRow.Font.Bold = True
End Sub
In both cases, it will have the following output:
Choose any of these methods to make the entire row bold using the VBA in Excel.
VBA to Make First Row Bold
Similar to the previous method, you can make just the first row of text or string bold using the Row number method or the EntireRow method.
Use any of the following code to make your first row bold:
Row Number Method:
Sub bold_first_row()
Rows(1).Font.Bold = True
End Sub
It will bold the entire row 1.
EntireRow Method:
Sub bold_entire_row2()
Range("A1").EntireRow.Font.Bold = True
End Sub
It will also bold the entire row 1.
How to Unbold Text or String Using VBA
Now, if you have read the whole article, you can easily unbold a text, string, or a range of cells. We set the Font.Bold property to True to bold any text string or cell. If you have a text string that is already bold, you can unbold it by setting the Font.Bold property to False.
The Generic Syntax:
Range.Font.Bold= False
Take a look at the following code:
Sub unbold_text()
Range("B5:B10").EntireRow.Font.Bold = False
End Sub
The above VBA code will unbold the specific range of cells:
In this way, you can unbold the text or string with the Excel VBA quite easily.
💬 Things to Remember
✎ To make a text or string bold, select that cell and set Font.Bold to True.
✎ To bold a specific range of cells, use the Range method.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to bold a text in string in Excel using VBA codes. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
Related Articles
- How to Write 001 in Excel (11 Effective Methods)
- Capitalize First Letter of Each Word in Excel (4 Ways)
- How to Change Lowercase to Uppercase in Excel Without Formula
- Change Case in Excel without a Formula (5 Ways)
- How to Capitalize Each Word in Excel (7 Ways)
- How to Format Cell and Center Text with Excel VBA (5 Ways)