How to color or highlight certain words in cells in Excel?
When working extensively with data in Excel, it’s often necessary to draw attention to specific words or keywords within cell content—such as terms, references, or statuses—to make spreadsheets easier to review and analyze. However, Excel is different from Word in that it does not provide a direct, built-in command for automatically highlighting or coloring just certain words inside a single cell. This limitation can make it challenging to emphasize important textual information or visually distinguish key terms within long text entries.
Fortunately, although no native feature exists for this purpose, there are several practical workarounds you can use to achieve the goal of coloring particular words inside cells. Depending on your experience level and specific scenario, you can either use VBA code to programmatically color keywords or opt for an add-in solution such as Kutools for Excel, which provides an intuitive and user-friendly way to mark keywords visually within a selected range.
This article will walk you through these methods step by step, explaining the benefits, use cases, and essential tips for each approach. This will help you efficiently highlight certain words in your Excel cells for clearer and more effective data presentation.
Color certain words in a range with VBA code
One effective way to color or highlight specific words inside cells is by using VBA (Visual Basic for Applications), Excel’s automation language. This method is particularly useful when you need batch processing or when working with large ranges where manual formatting would be inefficient.
Applicable scenarios: VBA solutions are especially helpful for skilled Excel users who want more control and flexibility. This approach works well for documents where keywords change frequently or when color-coding needs to be performed across multiple locations in a worksheet.
Limitations: VBA code will temporarily modify your worksheet and requires basic macro knowledge. Also, the provided code colors all matched words in red only—manual edits are needed to use other colors. Be aware that case sensitivity is ignored, and the macro cannot be undone using Ctrl+Z. Always save your workbook before running code and enable macros as needed.
To use VBA to color specific words within cells, follow these steps:
1. Select the cell range that contains the words you want to highlight. With the range selected, press the Alt + F11 keys together to launch the Microsoft Visual Basic for Applications editor. This is the platform where you can create and run VBA scripts.
2. In the VBA editor window, click on Insert > Module to create a new code module. In the right-hand pane, paste the following VBA code into the module window. This code will search your selected range for the keywords and apply color formatting to each found word within the cells.
VBA code: Color certain words in cells in Excel
Sub HighlightStrings()
Dim xHStr As String, xStrTmp As String
Dim xHStrLen As Long, xCount As Long, I As Long
Dim xCell As Range
Dim xArr
On Error Resume Next
xHStr = Application.InputBox("What is the string to highlight：", "KuTools For Excel", , , , , , 2)
If TypeName(xHStr) <> "String" Then Exit Sub
Application.ScreenUpdating = False
xHStrLen = Len(xHStr)
For Each xCell In Selection
xArr = Split(xCell.Value, xHStr)
xCount = UBound(xArr)
If xCount > 0 Then
xStrTmp = ""
For I = 0 To xCount - 1
xStrTmp = xStrTmp & xArr(I)
xCell.Characters(Len(xStrTmp) + 1, xHStrLen).Font.ColorIndex = 3
xStrTmp = xStrTmp & xHStr
Next
End If
Next
Application.ScreenUpdating = True
End Sub3. With the code in place, press the F5 key to run the script. When prompted by the Kutools for Excel input dialog, type the word or words you want to highlight (separate multiple words with commas if the script supports this). Click OK to execute.
After running the macro, Excel will immediately apply the specified color (default is red) to all instances of the entered keyword(s) within the selected cells, as demonstrated below:
Troubleshooting: If the macro does not run, ensure macros are enabled in your workbook and the selection is not locked. If you don’t see the changes, check that you entered the keywords correctly and that your range selection covers the intended cells.
Pros & Cons: VBA is flexible and reusable for batch tasks, but it requires comfort with the VBA editor. In collaborative environments, having to explain security prompts or macro requirements may be inconvenient.
Color certain words in a range with Kutools for Excel
If you prefer a method that avoids coding altogether, Kutools for Excel provides an accessible and convenient alternative. With its Mark Keyword feature, you can quickly color-highlight one or more keywords in your selected range, making it a practical choice for users who don’t want to use VBA.
Applicable scenarios: Kutools’ method is perfect for those who need to regularly highlight keywords, especially in collaborative settings or for users less familiar with macros. The interface is straightforward, and you can change color, enter multiple keywords at once, or apply matches with or without case sensitivity via checkboxes.
After installing Kutools for Excel, take the following steps:
- On the Excel Ribbon, go to Kutools > Text > Mark Keyword.
- In the Mark Keyword dialog box, set the following options:
- Confirm or adjust the data range you want to examine;
- Enter the word(s) to search for in the Keyword box. To highlight more than one keyword, separate them with commas (for example: "apple,peach,banana).
- Enable the Mark keyword colors option, and select a highlight color of your choice from the palette.
- Once your settings are ready, click OK to apply.
Once confirmed, Kutools will instantly apply your chosen highlight color to all occurrences of each keyword in the specified range, as shown here:
Both VBA and Kutools solutions enable you to visually emphasize keywords in Excel cells, enhancing readability and data interpretation. Decide which approach matches your workflow: VBA for automation and broader control, or Kutools for easy, interactive operation with no coding required.
If you encounter issues, such as keywords not being highlighted, check the entered terms for typos, ensure the extension is installed and enabled (if using Kutools), and confirm your data range selection.
If you want to learn more ways to customize Excel and boost productivity, visit our Excel tutorials library for even more practical techniques.
Related articles:
- How to highlight a cell if a value exists in or equals to cell in another column in Excel?
- How to compare two strings for similarity or highlight differences in Excel?
- How to make the sheet tab name equal to the cell value in Excel?
- How to change the value based on the cell color in Excel?
- How to change cell color when a cell is clicked or selected in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in