My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel VBA / Change the Color of Words in Text
Change the color of words in text
by

One of our students (hi Leah 🙂 ) sent me a question last week asking how she could change the color of words (sub-strings) in text. She was trying to visually indicate where words were on the sheet.

She supplied some code that she got somewhere on the net. I'm not sure where from so if you recognise it as your own, please let me know so I can attribute it to you.

It is a nice piece of code that uses regular expressions to locate a word in text, and then changes the color of that word. I made a few modifications/enhancements to expand its functionality.

Change color of words in text animation

What This VBA Does

Using this code will allow you to :

  • Find multiple words (the search is not case sensitive)
  • Change the color of each of those words (to different colors if you like)
  • Check a single cell, or any size range
  • Check for any text string

Because the code looks for a text string, you don't have to just look for real words, you can search for any text string, e.g. "INVOICE 1234".

I've written the code so that it uses the .CurrentRegion property to search all cells in the current region

What's the CurrentRegion?

Well, Microsoft's definition is 'The current region is a range bounded by any combination of blank rows and blank columns'

Let's look at a couple of examples. We have data in cells as per this image

Showing the Current Region

If I click into any cell in the range A1:C3 the CurrentRegion is A1:C3 because it is bounded by a blank row (4) which intersects a blank column (D)

Showing the Current Region - 2

If I click into any cell containing xxx the CurrentRegion is A1:E6, because the first blank row and blank column that bound cells containing xxx are Row 7 and Column F.

Showing the Current Region - 3

In fact if you click in the empty cells E5 or E6, the CurrentRegion is still A1:E6 because the first empty row and column bounding E5 or E6 are Row 7 and Column F. Likewise if you click into an empty cell on Row 4 or Column D, the CurrentRegion is A1:E6 for the same reason.

A Little Bonus

On Sheet 2 of the workbook you can download for this post (see below), I've set up a little macro to illustrate how CurrentRegion works. Just click any cell and then click the 'Show CurrentRegion' button.

Right, Back to Changing Word Colors

So you have your worksheet full of text, and you want to color various words in a rainbow that hopefully won't hurt your eyes.

The first thing you need to do is tell the VBA code what the list of words you are looking for are. Then next thing is tell it what colors to use for those words

At the top of the code you'll see these two lines :

    MyWords = VBA.Array("Sky", "Grass", "Ruby", "Panther")
    MyColors = VBA.Array(vbBlue, vbGreen, vbRed, vbMagenta)

Put whatever words you like in the MyWords array. Make sure you enclose them in double quotes, and separate each word with a comma.

Then in the MyColors array, list the colors you want to use for each word in the MyWords array. The first color will apply to the first word, the second color to the second word etc. So the word Sky will be vbBlue, Grass will be vbGreen, and so on.

You can have one word or a hundred, it's up to you. Just make sure you have exactly the same number of colors as you do words.

Color Constants

The MyColors array is using the Excel color constants, but it is possible to specify any color you like using a color's HEX value.

A lollipop to the first person who tells me how to do this.

MATCH Function

To find the word we want in our list the code uses Excel's MATCH function. Check that article if you are not familiar with how it works.

Conditional Formatting

Using conditional formatting you can't change the color of only part of the text string you are searching for, but you can use conditional formatting or conditional formatting with formulas to highlight cells containing a particular string, or to change the color of all the text in the cell.

The Code

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Change the color of words in text

More Excel VBA Posts

automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Static variables in VBA
Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
save chart as image
List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Clearing Downstream Dependent Data Validation Lists
Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
Excel Status Bar
Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
Progress Bar for Excel VBA
Create your own progress bar for VBA in Excel. Use it to show that your code is still running, and how long before it finishes.
error handling in vba
Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
Finding File Metadata Using FileSystemObject
Find file meta data like the creation date, last modified date and file size using Windows FileSystemObject in Excel VBA
Automatically Add Items to Data Validation List
Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points

More Excel VBA Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.




Category: Tag:

Reader Interactions

Comments

  1. Maggie

    Hey, Thanks for this wonderful code.

    I have a customer feedback data(their comments) in a column and i want to color the keyword that are in my different sheet (3 columns with different keywords about the service:- Good, bad and neutral keywords) (40-50 rows), like Good keywords in Green, Bad in Red and neutral in magenta.

    Can you help me with the code, that can find the those keywords and change their font color as per above.?

    This is the snapshot of output result.
    https://www.dropbox.com/s/71mq9796erid833/IMG_20200525_063440.jpg?dl=0

    Thanks in advance!!

    Reply

    • Philip Treacy

      Hi Maggie,

      I’d be happy to help but it’s easier to do with your workbook.

      Please start a topic on our forum and attach your workbook to that.

      Regards

      Phil

      Reply

    • Philip Treacy

      Hi Janice,

      Yes you can. At the top of the code you need to declare a new variable called MakeBold, so modify the existing statement to this

      Dim MyWords, MyColors, MakeBold

      Now, assign True or false values that correspond to the word(s) you want to make bold:

      MakeBold = VBA.Array(True, False, True, False)

      Our list of words is (“Sky”, “Grass”, “Ruby”, “Panther”) so the True/False values above mean we want Sky and Ruby to be bold.

      Lastly, add a line to the existing If statement below to make the word(s) bold:

      If Not IsError(MatchPosition) Then
                        
MyCell.Characters(MyObj.firstindex + 1, MyObj.Length).Font.Color = MyColors(MatchPosition - 1)
MyCell.Characters(MyObj.firstindex + 1, MyObj.Length).Font.Bold = MakeBold(MatchPosition - 1)
                    
End If

      Regards

      Phil

      Reply

  4. Mike

    I am still intrigued by the ability to highlight specific words/phases in a cell. Here is another twist that I am struggling with – is it possible to highlight all “occurrences” of a word/phrase in a cell. I am working with a worksheet drawn from an external program that contains large amounts of text in cells in a column (statements made by people about certain events). I currently search for a particular word/phrase using a text filter. Now I wish to highlight “all” occurrences of that word/phrase in each one of the filtered cells rather than only the first occurrence in each cell. For example, all occurrences of “walk” in a cell containing the phrase “While walking on the sidewalk near the boardwalk, I met Bob walking the other way.” “walk shows up 4 times (“walk”ing, side”walk”, board”walk”, “walk:ing) and I would like each instance to be highlighted.

    Thanks in advance to any suggestions you might offer.

    Reply

    • Catalin Bombea

      The key is the Instr function.
      On a normal use, the first parameter of Instr function is 1 (this indicates the position of the character to start with)
      If we want to check AFTER the first match, (Instr returns only the position of first match), then we have to tell instr to start comparing AFTER the position of the first match. The loop will run until Instr returns 0, this indicates that there are no more matches. The trick is done by using the StartPos parameter, which increases with every occurence found: StartPos = StartPos + TestStr, and passing this parameter to instr function for the next loop: TestStr = InStr(StartPos, MyCell.Text, KeyWords(i, 1), vbTextCompare)
      The link to the workbook provided in previous message can be used to download this new version.

      Here is the code:
      Sub ColorWords()
      ' Written by Philip Treacy, Sep 2014
      ' My Online Training Hub https://www.myonlinetraininghub.com/change-the-color-of-words-in-text

      Dim KeyWords As Variant
      Dim MyCell As Range, TargetRange As Range
      Dim i As Integer, j As Integer, StartPos As Integer
      Dim TestStr As Long

      KeyWords = Range("Keywords[[Keywords]:[Color Index]]") 'Add to list as required

      Set TargetRange = ActiveCell.CurrentRegion

      TargetRange.Font.ColorIndex = xlAutomatic

      For Each MyCell In TargetRange.Cells
      For i = 1 To UBound(KeyWords, 1)
      StartPos = 1
      TestStr = 1
      Do Until TestStr = 0
      TestStr = InStr(StartPos, MyCell.Text, KeyWords(i, 1), vbTextCompare)
      If TestStr Then MyCell.Characters(TestStr, Len(KeyWords(i, 1))).Font.ColorIndex = KeyWords(i, 2)
      StartPos = StartPos + TestStr
      Loop
      Next i
      Next MyCell

      End Sub
      Catalin

      Reply

      • Mike

        Awesome! I just tested your code out and it does exactly what I was wanting. I have adapted it to an application I have so that my users can search for a word/phrase and have their terms highlighted throughout the results and across four different worksheets!.

        How come you guys are so smart?

        Thanks for helping me look at least a little smart. This has been a good day!

        Reply

  5. Leonid Koyfman

    I modified your code to solve it without RegExp.

    What is advantage of using RegExp in this case or it’s just matter of style?

    Sub ColorWords2()
    ' Written by Leonid
    
    Dim MyWords, MyColors

    Dim MyCell As Range, TargetRange As Range
    Dim word, pos, i
     
    MyWords = VBA.Array("Sky", "Grass", "Ruby", "Panther")      'Add to list as required
    MyColors = VBA.Array(vbBlue, vbGreen, vbRed, vbMagenta)     'Add corresponding color to match MyWords list
     
    Set TargetRange = ActiveCell.CurrentRegion
     
    TargetRange.Font.ColorIndex = xlAutomatic

    For Each MyCell In TargetRange.Cells
        i = 0
        MyCell = " " & MyCell & " "
        For Each word In MyWords
            pos = InStr(1, MyCell, " " & word & " ")
            If pos > 0 Then
                MyCell.Characters(pos, Len(word)).Font.Color = MyColors(i)
            End If
            i = i + 1
        Next word
    Next MyCell
     
End Sub
    Reply

    • Philip Treacy

      Hi Leonid,

      I guess it’s horses for courses. Your way works just as well as using a regex expression.

      I did make a few modifications to your code though. I removed this line which I found was adding spaces to either end of the text in the cell

      MyCell = " " & MyCell & " "

      and by specifying vbTextCompare for InStr it made the search case-insensitive.

      Also, as I have commas separating my words, I found that searching for ” ” & word & ” “ wasn’t finding a match. So I’ve assumed that there will be some kind of separator between words, whether its a space, a comma or whatever, and I am just searching using this

      InStr(1, MyCell, word, vbTextCompare)

      Of course doing it this way means that if I search for sky I’ll match Sky and Husky.

      Thanks for taking the time to contribute your solution, much appreciated.

      Cheers

      Phil

      Sub ColorWords2()
    ' Written by Leonid
    
    Dim MyWords, MyColors

    Dim MyCell As Range, TargetRange As Range
    Dim word, pos, i
     
    MyWords = VBA.Array("Sky", "Grass", "Ruby", "Panther")      'Add to list as required
    MyColors = VBA.Array(vbBlue, vbGreen, vbRed, vbMagenta)     'Add corresponding color to match MyWords list
     
    Set TargetRange = ActiveCell.CurrentRegion
     
    TargetRange.Font.ColorIndex = xlAutomatic

    For Each MyCell In TargetRange.Cells
        i = 0      
        For Each word In MyWords
            pos = InStr(1, MyCell, word, vbTextCompare)  'Added vbTextCompare
            If pos > 0 Then
                MyCell.Characters(pos, Len(word)).Font.Color = MyColors(i)
            End If
            i = i + 1
        Next word
    Next MyCell
     
End Sub
      Reply

      • Rick Rothstein (MVP - Excel)

        You said…

        I did make a few modifications to your code though. I removed this line which I found was adding spaces to either end of the text in the cell
        MyCell = ” ” & MyCell & ” ”

        You do not want to do that. Notice the added spaces in this line of code…

        pos = InStr(1, MyCell, ” ” & word & ” “)

        The spaces that were added to MyCell allowed InStr to be able to find the “word” at the beginning or at the end of the text as well as internally to the text. Now it was also mentioned that the code the above is from only works if spaces are delimiting the words. I thought you and your readers might be interested in this function which will find a word as a stand-alone word only and will not be tripped up by the word being searched for if it was part of another word. Hence, the following function will correctly find “other” in the text even if words like “brother” or “mother” appear before it. One note, though… the function assume normal English letters; that is, accented letters are treated like non-letters. Okay, with that said, here is the function (note the Start position argument is third in the list, not first, but it is optional.

        Function InStrExact(ByVal SearchText As String, _
        ByVal FindMe As String, _
        Optional ByVal Start As Long = 1, _
        Optional ByVal MatchCase As String = False) As Long
        Dim X As Long, Pattern As String
        Dim Str1 As String, Str2 As String
        If MatchCase Then
        Str1 = SearchText
        Str2 = FindMe
        Pattern = “[!A-Za-z0-9]”
        Else
        Str1 = UCase(SearchText)
        Str2 = UCase(FindMe)
        Pattern = “[!A-Z0-9]”
        End If
        For X = Start To Len(Str1) – Len(Str2) + 1
        If Mid(” ” & Str1 & ” “, X, Len(Str2) + 2) _
        Like Pattern & Str2 & Pattern Then
        InStrExact = X
        Exit Function
        End If
        Next
        End Function

        Reply

        • Philip Treacy

          Hi Rick,

          When I run the code as supplied by Leonid it adds spaces before and after the text in each cell, so you have to remove the 

          MyCell = " " & MyCell & " "

          or the text gets a space added before and after it each time the code is run.

          Additionally, I found the code did not work and I got results like this, which also show the space added at the start of each string,
          incorrect results

          when of course the results should look like this
          correct results

          The code as I modified it does work to find strings within strings, as shown here by the text in row 2, I didn’t say it had to be delimited by spaces.
          correct substrings

          Sorry I couldn’t get your function to work, it returned 0. But I found that just using 

          InStr(Start, SearchText, FindMe, vbBinaryCompare)

          worked for case sensitive searches and 

          InStr(Start, SearchText, FindMe, vbTextCompare)

          worked for case-insensitive searches.

          You can also use Find() in the worksheet for case-insensitive searches.

          Regards

          Phil

          Reply

    • Catalin Bombea

      Hi Glenn,
      It’s a data type declaration:
      Data types:
      @ – currency
      # – double
      % – integer
      & – long
      ! – single
      $ – string
      Usually, functions without $ sign at the end of function name returns a Variant data type with subtype String, and the FunctionName$() functions returns a String data type.
      This way, functions can be more efficient.
      Catalin

      Reply

  8. Mike

    Is it possible to highlight just a sequence of chararcters or partial word? For example, I would like to have only “pant” highlighted rather than the whole word “panther” or “ky” rather than “sky”.

    Reply

    • Catalin Bombea

      Hi Mike,
      Almost anything is possible, sorry for the late reply.
      You can try this file from our OneDrive folder
      The modified code is: (works with full or partial words)
      Option Explicit
      Option Compare Text
      Sub ColorWords()
      ' Written by Philip Treacy, Sep 2014
      ' My Online Training Hub https://www.myonlinetraininghub.com/change-the-color-of-words-in-text

      Dim MyWords, MyColors
      Dim MyCell As Range, TargetRange As Range
      Dim i As Integer
      Dim TestStr As Long

      MyWords = VBA.Array("Sky", "Grass", "Ruby", "Panth") 'Add to list as required
      MyColors = VBA.Array(vbBlue, vbGreen, vbRed, vbMagenta) 'Add corresponding color to match MyWords list

      Set TargetRange = ActiveCell.CurrentRegion

      TargetRange.Font.ColorIndex = xlAutomatic

      For Each MyCell In TargetRange.Cells
      For i = LBound(MyWords) To UBound(MyWords)
      TestStr = InStr(1, MyCell.Text, MyWords(i), vbTextCompare)
      If TestStr Then MyCell.Characters(TestStr, Len(MyWords(i))).Font.Color = MyColors(i)
      Next i
      Next MyCell

      End Sub
      Sub ShowCurrentRegion()

      ActiveCell.CurrentRegion.Select

      End Sub

      Reply

      • Mike

        Thanks Catalin

        I very much appreciate the reply. Your code worked well and I was able to adapt my specific case – however my need changed so will be unable to implement. never-the-less, I did learn something and may be able to implement in the future so a valuable exercise for me. Thanks again – it is good to know that help (and good help!) is just a question away. Have a good day.

        Reply

          • Mike

            This tip has certainly drawn at lot of attention – it is a good one. I have another question – is it possible to highlight all occurrences of a word/phase/sequence of letters by entering the search criteria into worksheet cell rather than into the code?

          • Catalin Bombea

            Hi Mike,
            Of course it’s possible, here is a version using a table containing keywords and colors, you can choose any color by typing a number from 1 to 56 in column J. The colors are listed in sheet3 of this file from our OneDrive folder.
            The code:
            Sub ColorWords()
            ' Written by Philip Treacy, Sep 2014
            ' My Online Training Hub https://www.myonlinetraininghub.com/change-the-color-of-words-in-text

            Dim KeyWords As Variant
            Dim MyCell As Range, TargetRange As Range
            Dim i As Integer
            Dim TestStr As Long

            KeyWords = Range("Keywords[[Keywords]:[Color Index]]") 'Add to list as required

            Set TargetRange = ActiveCell.CurrentRegion

            TargetRange.Font.ColorIndex = xlAutomatic

            For Each MyCell In TargetRange.Cells
            For i = 1 To UBound(KeyWords, 1)
            TestStr = InStr(1, MyCell.Text, KeyWords(i, 1), vbTextCompare)
            If TestStr Then MyCell.Characters(TestStr, Len(KeyWords(i, 1))).Font.ColorIndex = KeyWords(i, 2)
            Next i
            Next MyCell

            End Sub
            The only change is that we create the array, using a defined table range :
            KeyWords = Range(“Keywords[[Keywords]:[Color Index]]”)
            A range is a bidimensional array, by default, because we have rows and columns.
            This is the reason that we search for values in column 1 of the table with KeyWords(i, 1), and return values from column 2 , that holds the color index: KeyWords(i, 2)
            Hope it’s clear 🙂
            Catalin

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *