Highlight single words in multiple Excel cells

When you've got a spreadsheet with many rows of data, and a lot of text in each cell, and you want to focus on a particular word or phrase, it can be helpful to have that word or phrase highlighted in a different font color. The trouble is if you try to find and replace for the string, not only the string but all of the text in any cell in which it appears will change color.

A plausible workaround is to copy and paste the data from Excel into Word, run find & replace to change the color of the string and then re-paste back into Word. However if you've got a spreadsheet with thousands of rows this may be more work than Word can handle.

Luckily, VBA code has been posted here by Paul Edstein which does the trick.

1. First select the column or columns which contain the word or phrase you need to change to red text.

2. Press ALT + F11 to enter Visual Basic. Right click on the spreadsheet name in the project list and enter a new module. Put the below code in the module. [it may be necessary to copy this code from the site I've linked to. My Wix editor messes with the formatting.]

Sub HighlightStrings() Application.ScreenUpdating = False Dim Rng As Range, StrFnd As String, StrTmp As String, i As Long, j As Long, x As Long StrFnd = InputBox("What is the string to highlight", "Highlighter") x = Len(StrFnd) For Each Rng In Selection With Rng j = UBound(Split(Rng.Value, StrFnd)) If j > 0 Then StrTmp = "" For i = 0 To j - 1 StrTmp = StrTmp & Split(Rng.Value, StrFnd)(i) .Characters(Start:=Len(StrTmp) + 1, Length:=x).Font.ColorIndex = 3 StrTmp = StrTmp & StrFnd Next End If End With Next Rng Application.ScreenUpdating = True End Sub

3. The code doesn't need to be edited. Just press play and a dialog box will appear prompting you to enter the string you want highlighted.

4. Enter your string and click OK and the string text will be changed to red. Note that the VBA code is case sensitive.