Highlighting one word in a cell

[Jimmy-W]

I have a column that contains strings of text. I'd like to highlight (in color) one word that appears in numerous column cells. The search/replace function doesn't seem to allow me to do that. So, for example, how can I find "text" and replace it with text? Thanks.

[Xircal]

It says here that Excel doesn't provide an option to do that by the looks of it: http://office.microsoft.com/en-us/ex...001174200.aspx

There's a third party tool here which might help, but it's only free for the 15-day trial period: http://www.digdb.com/excel_add_ins/s...ions_wildcard/

[Jimmy-W]

Thanks. I got a copy, but I don't see where it does what I want, or even do a find/replace.

[Xircal]

There's a macro on this site which might do the job instead: http://www.ozgrid.com/forum/showthread.php?t=66197

[RetiredGeek]

Jimmy,

Here's the code from the link provided by Xircal adapted to do color:

Option Explicit

Sub Highlight()

   Dim rCell    As Range
   Dim zFind    As String
   Dim lFindLen As Long
   
   zFind = "test"
   lFindLen = Len(zFind)
   
   For Each rCell In Selection
   
      With rCell
          .Characters(Start:=InStr(.Value, zFind), Length:=lFindLen) _
          .Font.ColorIndex = 3  'Red -- Yellow too hard to read!
      End With

   Next rCell
   
End Sub

Note: Normally to get whole words only, e.g. Test and NOT Testing or retest, you would add a space before/after the word, e.g. " test ". However, this messes up this code if it is the first or last word in the group and would take quite a bit of logic to correct (I made an attempt but it stumped me this early in the moring, sorry).

Almost forgot you have to select the cells you want to have it operate on. This can be set other ways, like a range name.

[Jimmy-W]

Thanks, everyone. The macro works, but not perfectly, at least in the spreadsheet that I have. First, if the word appears twice in a cell, it only highlights (changes font color) one of the words (the first). Also, it highlights other words or strings when such strings appear at the beginning of the cell. I attached an example. My selected word is MySearch123.

[Jimmy-W]

Well, I have a workaround. I copy the data from Excel and paste it into Word and create a table. Then, I find/replace the word with formatting, e.g,. red type. Then, I copy the entire table and paste it into Excel, and the formatting remains.

[WebGenii]

I'm curious about what the scenario is that requires this solution?

[macropod]

I too am curious. Be that as it may, try:

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

NOTE: The above code has no regard to whether the string matched is a complete word. Additional logic would be required for that.

[JohnFleming]

You CAN do a highlight of the text colour and attributes (bold, italic) by in-cell editing. Select the text to be changed, right-click, and you can change font colour and attributes, but it seems that you can not change the fill colour. You certainly cannot do find-and-replace.