Forum Discussion
Highlight or Change the font color for only a specific word and not the entire cell in Excel?
- Mar 25, 2024
You can highlight or change the font color for specific words within a cell in Excel using conditional formatting. Here's how you can do it:
1. Select the Range: First, select the range of cells that you want to apply the conditional formatting to.
2. Conditional Formatting Rules:
- Go to the "Home" tab on the Excel ribbon.
- Click on "Conditional Formatting" in the "Styles" group.
- Choose "New Rule" from the dropdown menu.
3. Create a Formula-Based Rule:
- In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
- Enter the formula to check if the specific word exists in the cell. For example, if you want to highlight cells containing the word "Apple", you can use a formula like:
=ISNUMBER(SEARCH("Apple", A1))
- Replace "Apple" with the word you want to highlight and A1 with the cell reference containing the text.
4. Format the Cells:
- Click on the "Format" button to choose the formatting options. You can select the desired font color, fill color, or other formatting options.
- Once you've configured the formatting options, click "OK" to apply them.
5. Finalize the Rule:
- Review the preview of the formatting in the "Preview" box.
- Click "OK" to apply the conditional formatting rule to the selected range of cells.
Now, Excel will automatically highlight or change the font color for any cell containing the specified word. Repeat the above steps for each word or phrase you want to highlight. You can also add multiple conditional formatting rules to achieve different formatting effects for different words.
You can use VBA too.
Here's a VBA solution to accomplish this task:
Sub HighlightSpecificWords() Dim rng As Range Dim cell As Range Dim searchText As String ' Define the search text (word or phrase to highlight) searchText = "Apple" ' Set the range of cells to search Set rng = Range("A1:A100") ' Change this range to match your data ' Loop through each cell in the range For Each cell In rng ' Check if the search text exists in the cell If InStr(1, cell.Value, searchText, vbTextCompare) > 0 Then ' If found, apply formatting to the specific word HighlightSpecificWord cell, searchText End If Next cell End Sub Sub HighlightSpecificWord(cell As Range, searchText As String) Dim startPos As Integer Dim endPos As Integer Dim wordLength As Integer ' Find the starting position of the search text in the cell startPos = InStr(1, cell.Value, searchText, vbTextCompare) ' Calculate the ending position of the search text endPos = startPos + Len(searchText) - 1 ' Highlight the specific word by changing the font color With cell.Characters(startPos, Len(searchText)).Font .Color = RGB(255, 0, 0) ' Change the RGB values to specify the desired font color ' You can also apply other formatting options here, such as bold, italic, etc. End With End Sub
To use this VBA code:
- Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
- Go to Insert > Module to insert a new module.
- Copy and paste the above code into the module window.
- Modify the searchText variable to specify the word or phrase you want to highlight.
- Adjust the range in the HighlightSpecificWords subroutine to match the range of cells you want to search.
- Close the VBA editor.
- Run the HighlightSpecificWords subroutine by pressing F5 or going to Run > Run Sub/UserForm.
This VBA code will search through the specified range of cells, find the specific word or phrase, and highlight it by changing the font color. You can customize the formatting options according to your preferences by modifying the With statement inside the HighlightSpecificWord subroutine. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
You can highlight or change the font color for specific words within a cell in Excel using conditional formatting. Here's how you can do it:
1. Select the Range: First, select the range of cells that you want to apply the conditional formatting to.
2. Conditional Formatting Rules:
- Go to the "Home" tab on the Excel ribbon.
- Click on "Conditional Formatting" in the "Styles" group.
- Choose "New Rule" from the dropdown menu.
3. Create a Formula-Based Rule:
- In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
- Enter the formula to check if the specific word exists in the cell. For example, if you want to highlight cells containing the word "Apple", you can use a formula like:
=ISNUMBER(SEARCH("Apple", A1))
- Replace "Apple" with the word you want to highlight and A1 with the cell reference containing the text.
4. Format the Cells:
- Click on the "Format" button to choose the formatting options. You can select the desired font color, fill color, or other formatting options.
- Once you've configured the formatting options, click "OK" to apply them.
5. Finalize the Rule:
- Review the preview of the formatting in the "Preview" box.
- Click "OK" to apply the conditional formatting rule to the selected range of cells.
Now, Excel will automatically highlight or change the font color for any cell containing the specified word. Repeat the above steps for each word or phrase you want to highlight. You can also add multiple conditional formatting rules to achieve different formatting effects for different words.
You can use VBA too.
Here's a VBA solution to accomplish this task:
Sub HighlightSpecificWords()
Dim rng As Range
Dim cell As Range
Dim searchText As String
' Define the search text (word or phrase to highlight)
searchText = "Apple"
' Set the range of cells to search
Set rng = Range("A1:A100") ' Change this range to match your data
' Loop through each cell in the range
For Each cell In rng
' Check if the search text exists in the cell
If InStr(1, cell.Value, searchText, vbTextCompare) > 0 Then
' If found, apply formatting to the specific word
HighlightSpecificWord cell, searchText
End If
Next cell
End Sub
Sub HighlightSpecificWord(cell As Range, searchText As String)
Dim startPos As Integer
Dim endPos As Integer
Dim wordLength As Integer
' Find the starting position of the search text in the cell
startPos = InStr(1, cell.Value, searchText, vbTextCompare)
' Calculate the ending position of the search text
endPos = startPos + Len(searchText) - 1
' Highlight the specific word by changing the font color
With cell.Characters(startPos, Len(searchText)).Font
.Color = RGB(255, 0, 0) ' Change the RGB values to specify the desired font color
' You can also apply other formatting options here, such as bold, italic, etc.
End With
End Sub
To use this VBA code:
- Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
- Go to Insert > Module to insert a new module.
- Copy and paste the above code into the module window.
- Modify the searchText variable to specify the word or phrase you want to highlight.
- Adjust the range in the HighlightSpecificWords subroutine to match the range of cells you want to search.
- Close the VBA editor.
- Run the HighlightSpecificWords subroutine by pressing F5 or going to Run > Run Sub/UserForm.
This VBA code will search through the specified range of cells, find the specific word or phrase, and highlight it by changing the font color. You can customize the formatting options according to your preferences by modifying the With statement inside the HighlightSpecificWord subroutine. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Thank you!
This worked great. Since I am not a VBA master, would you be able to explain in laymens terms what each line of code means and how to modify it for any changes. Is this code only finding the first "word" or all "words"?
Thanks again!
- NikolinoDEMar 27, 2024Gold Contributor
Let's break down the code and explain it in simply terms:
vba code is untested, please backup your file.
Sub HighlightSpecificWords() Dim rng As Range Dim cell As Range Dim searchText As String ' Define the search text (word or phrase to highlight) searchText = "Apple" ' Set the range of cells to search Set rng = Range("A1:A100") ' Change this range to match your data ' Loop through each cell in the range For Each cell In rng ' Check if the search text exists in the cell If InStr(1, cell.Value, searchText, vbTextCompare) > 0 Then ' If found, apply formatting to the specific word HighlightSpecificWord cell, searchText End If Next cell End Sub Sub HighlightSpecificWord(cell As Range, searchText As String) Dim startPos As Integer Dim endPos As Integer Dim wordLength As Integer ' Find the starting position of the search text in the cell startPos = InStr(1, cell.Value, searchText, vbTextCompare) ' Calculate the ending position of the search text endPos = startPos + Len(searchText) - 1 ' Highlight the specific word by changing the font color With cell.Characters(startPos, Len(searchText)).Font .Color = RGB(255, 0, 0) ' Change the RGB values to specify the desired font color ' You can also apply other formatting options here, such as bold, italic, etc. End With End Sub
Now, let's explain each part of the code and how to modify it:
- Sub HighlightSpecificWords():
- This is the main subroutine that will be executed when you run the macro. It loops through each cell in the specified range and checks for the presence of the search text.
- Dim rng As Range / Dim cell As Range:
- These lines declare two variables (rng and cell) to represent the range of cells to search and each individual cell within that range.
- Dim searchText As String:
- This line declares a variable (searchText) to store the word or phrase you want to search for and highlight.
- searchText = "Apple":
- Here, you define the search text. You can change "Apple" to any word or phrase you want to search for. You can also modify this line to read the search text from a specific cell, such as searchText = Range("B1").Value.
- Set rng = Range("A1:A100"):
- This line sets the range of cells to search. You can change "A1:A100" to specify the range where your data is located.
- For Each cell In rng: ... Next cell:
- This loop iterates through each cell in the specified range (rng).
- If InStr(1, cell.Value, searchText, vbTextCompare) > 0 Then:
- This line checks if the search text (searchText) exists within the current cell (cell.Value). If it does, it proceeds to highlight the word.
- Sub HighlightSpecificWord(cell As Range, searchText As String):
- This subroutine highlights the specific word within the cell.
- startPos = InStr(1, cell.Value, searchText, vbTextCompare):
- This line finds the starting position of the search text within the cell.
- endPos = startPos + Len(searchText) - 1:
- This line calculates the ending position of the search text.
- With cell.Characters(startPos, Len(searchText)).Font:
- This block of code applies formatting to the specific word by changing its font color.
- .Color = RGB(255, 0, 0):
- This line sets the font color to red (RGB values: 255 for red, 0 for green, 0 for blue). You can change these values to specify the desired font color.
You can modify the code according to your needs by changing the search text, the range of cells to search, and the formatting options. Additionally, you can modify the code to read the search text from a specific cell by referencing that cell's address. For example, you can use searchText = Range("B1").Value to read the search text from cell B1. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
I hope that the text and the steps helped you a little to understand the VBA code.
- SherrieSep 04, 2025Copper Contributor
I am trying to search and highlight multiple words, but they would not be a phrase, just multiple individual words in my excel. I am able to get this VBA to work on one word, but how can I make this (or a different) VBA work to search for multiple words such as "Apple" and "Banana" and "Orange"?
- NikolinoDESep 05, 2025Gold Contributor
Change this part of the code to your liking.
' Define the array of words to highlight searchWords = Array("Apple", "Banana", "Orange") ' Add more words as needed
- PaulMoorNov 14, 2024Copper Contributor
Hello,
Is it possible to change the colour of only the words that are in bold within a group of cells?
I am working on a very large spreadsheet and it would take an enormous amount of time to apply this manually.
Any assistance would be greatly appreciated.
- erin_porricelliMay 31, 2024Copper Contributor
NikolinoDE this is so helpful, thank you!
is it possible to modify the code to have it search for multiple words, then apply different colors to each of the words?
example - i want to search for apple, banana, cherry. i want apple to be red, banana to be yellow, and cherry to be blue. can this be done?
thank you!
- NikolinoDEJun 01, 2024Gold Contributor
It is possible to modify the VBA code to search for multiple words and apply different colors to each word. Below is an example of how you can achieve this:
- Create a dictionary to store the words to search for and their corresponding colors.
- Loop through each word in the dictionary and apply the formatting if the word is found in a cell.
Here is the modified VBA code:
Vba Code is untested, please backup your file.
Sub HighlightMultipleWords() Dim rng As Range Dim cell As Range Dim searchWords As Variant Dim word As Variant Dim colorDict As Object Dim i As Integer ' Create a dictionary to store words and their corresponding colors Set colorDict = CreateObject("Scripting.Dictionary") colorDict.Add "Apple", RGB(255, 0, 0) ' Red colorDict.Add "Banana", RGB(255, 255, 0) ' Yellow colorDict.Add "Cherry", RGB(0, 0, 255) ' Blue ' Set the range of cells to search Set rng = Range("A1:A100") ' Change this range to match your data ' Loop through each cell in the range For Each cell In rng ' Loop through each word in the dictionary For i = 0 To colorDict.Count - 1 word = colorDict.Keys(i) ' Check if the search text exists in the cell If InStr(1, cell.Value, word, vbTextCompare) > 0 Then ' If found, apply formatting to the specific word HighlightSpecificWord cell, word, colorDict(word) End If Next i Next cell End Sub Sub HighlightSpecificWord(cell As Range, searchText As String, color As Long) Dim startPos As Integer Dim endPos As Integer Dim wordLength As Integer ' Find the starting position of the search text in the cell startPos = InStr(1, cell.Value, searchText, vbTextCompare) ' Highlight all instances of the specific word by changing the font color Do While startPos > 0 ' Highlight the specific word by changing the font color With cell.Characters(startPos, Len(searchText)).Font .Color = color ' You can also apply other formatting options here, such as bold, italic, etc. End With ' Find the next instance of the search text in the cell startPos = InStr(startPos + Len(searchText), cell.Value, searchText, vbTextCompare) Loop End Sub
Explanation:
- Create a Dictionary: The dictionary colorDict stores the words to search for and their corresponding RGB colors.
- Loop Through the Range: The code loops through each cell in the specified range.
- Loop Through the Dictionary: For each cell, it loops through the dictionary of words and colors.
- Check and Highlight: If a word is found in the cell, it calls the HighlightSpecificWord subroutine to apply the specified color.
- Highlight Specific Word: The HighlightSpecificWord subroutine highlights all instances of the word in the cell by changing the font color to the specified color.
This code will search for multiple words and apply different colors to each of the words as specified in the dictionary. Adjust the colorDict entries and the range as needed for your specific use case.
The text, steps and code were created with the help of AI.
*Next time please open a new post, this would also help other participants.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Like it!
This will help all forum participants.