Format Specific Words in a Cell using VBA

In this blog post, we look at how to create a macro that will look in a list and format all the instances of specific words that appear in a cell.

This macro is case sensitive and will also only apply the formatting if it is the whole word. For example, if you are formatting all instances of the word ‘red’, this macro will not format it if used in words like ‘reddish’ and ‘sacred’.

The macro uses a For Each Next loop to work on the range of cells selected by a user.

The Characters property has been used to apply the formatting to only specific words in a cell. In order to work, the Characters property will need the position of the first character of the word, and also how many characters to format.

The InStr function was used to find the first character of the word, or words, in each cell.

A Do loop is used to check the whole cell so if the word occurs multiple times in a cell, each instance is formatted.

Here is the complete Excel VBA macro code used.

Sub HighlightText()
Dim rng As Range
Dim words As String
Dim NumChars As Long
Dim StartChar As Long
Dim rngChar As Long
Dim EndWords As Long
On Error Resume Next
words = InputBox("Please enter the word(s) to format", "Enter the words")
NumChars = Len(words)
    For Each rng In Selection
        rngChar = Len(rng)
        StartChar = InStr(1, rng, words)
        Do Until StartChar >= rngChar Or StartChar = 0
            EndWords = StartChar + NumChars
            If Mid(rng, StartChar - 1, 1) = " " Or StartChar = 1 Then
                If Mid(rng, EndWords, 1) = " " Or EndWords >= rngChar Then
                With rng.Characters(Start:=StartChar, Length:=NumChars).Font
                    .FontStyle = "Bold"
                    .Color = -16776961
                End With
                End If
            End If
            StartChar = InStr(EndWords, rng, words)
        Loop
    Next
   
End Sub

More Excel VBA Tutorials

• Loop through all the files of a folder using VBA
• Spell check a protected Excel worksheet
• Automatically create a table of contents for your spreadsheet