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