VBA Code To Highlight All Instances of Word/Phrase Within A Range
What This VBA Code Does
Have you ever had to search through a large Excel spreadsheet to find specific text?
It can be a time-consuming and frustrating task, especially if you’re looking for multiple instances of the same word or phrase. Fortunately, with a bit of VBA code, you can automate this process and save yourself a lot of time and effort.
One common use case for VBA macros in Excel is to find and highlight specific text within a spreadsheet. For example, imagine you have a spreadsheet containing a list of fruit names, and you need to quickly find and emphasize any cells that contain the word “Orange”. This could be useful for identifying which products to order, which suppliers to contact, or which inventory items to inspect.
With the VBA macro we’ll be discussing in this post, you can easily search through a range of cells and bold all instances of the word “Orange”. This can help you quickly identify and organize your data, and save you valuable time in the process. Let’s dive into the code and see how it works.
VBA Code To Bold Text Occurrences
The following VBA macro code will loop through a given range of cells and bold all instances of a provided search word (in this case “Orange”). The VBA code is specifically written to find multiple cases where the search word occurs more than once in a given cell.
Sub BoldWord()
'PURPOSE: Bold all instances of a specified text within a range
'SOURCE: www.TheSpreadsheetGuru.com
Dim Position As Long
Dim cell As Range
Dim SearchRange As Range
Dim SearchWord As String
'Inputs
Set SearchRange = ActiveSheet.Range("A1:A10")
SearchWord = "Orange"
'Ensure all text is unbolded
SearchRange.Font.Bold = False
'Loop through and bold any occurance of the search word
For Each cell In Range("A1:A10")
'Find first intance of search word
Position = InStr(1, cell, SearchWord, vbTextCompare)
'Loop in case of multiple instances
Do While Position > 0
cell.Characters(Position, Len(SearchWord)).Font.Bold = True
Position = InStr(Position + 1, cell, SearchWord, vbTextCompare)
Loop
Next cell
End SubVBA Code To Highlight Text Occurrences
The following VBA macro code will loop through a given range of cells and highlight all instances of a provided search word (in this case “Orange”) with the font color of your choosing. In the below example, the text will turn red. The VBA code is specifically written to find multiple cases where the search word occurs more than once in a given cell.
Sub HighlightWord()
'PURPOSE: Change Font Color Red for all instances of a specified text within a range
'SOURCE: www.TheSpreadsheetGuru.com
Dim Position As Long
Dim HighlightColor As Long
Dim cell As Range
Dim SearchRange As Range
Dim SearchWord As String
'Inputs
Set SearchRange = ActiveSheet.Range("A1:A10")
SearchWord = "Orange"
HighlightColor = RGB(255, 0, 0)
'Ensure all text is not highlighted
SearchRange.Font.ColorIndex = xlAutomatic
'Loop through and color any occurance of the search word
For Each cell In Range("A1:A10")
'Find first intance of search word
Position = InStr(1, cell, SearchWord, vbTextCompare)
'Loop in case of multiple instances
Do While Position > 0
cell.Characters(Position, Len(SearchWord)).Font.Color = HighlightColor
Position = InStr(Position + 1, cell, SearchWord, vbTextCompare)
Loop
Next cell
End SubUsing VBA Code Found On The Internet
Now that you’ve found some VBA code that could potentially solve your Excel automation problem, what do you do with it? If you don’t necessarily want to learn how to code VBA and are just looking for the fastest way to implement this code into your spreadsheet, I wrote an article (with video) that explains how to get the VBA code you’ve found running on your spreadsheet.
Getting Started Automating Excel
Are you new to VBA and not sure where to begin? Check out my quickstart guide to learning VBA. This article won’t overwhelm you with fancy coding jargon, as it provides you with a simplistic and straightforward approach to the basic things I wish I knew when trying to teach myself how to automate tasks in Excel with VBA Macros.
Also, if you haven’t checked out Excel’s latest automation feature called Power Query, I have put together a beginner’s guide for automating with Excel’s Power Query feature as well! This little-known built-in Excel feature allows you to merge and clean data automatically with little to no coding!
How Do I Modify This To Fit My Specific Needs?
Chances are this post did not give you the exact answer you were looking for. We all have different situations and it’s impossible to account for every particular need one might have. That’s why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to get quick answers to complex problems in Excel, PowerPoint, VBA, you name it!
I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee that 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don’t have time to fit everyone’s questions in (there never seem to be quite enough hours in the day!).
I wish you the best of luck and I hope this tutorial gets you heading in the right direction!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.