• Highlighting one word in a cell


    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.

    Viewing 6 reply threads
    • #1335886

      It says here that Excel doesn’t provide an option to do that by the looks of it: http://office.microsoft.com/en-us/excel-help/highlight-cells-HP001174200.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/select_cells_conditions_wildcard/

    • #1335888

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

    • #1336034

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

    • #1336061


      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). :cheers:

      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.

      May the Forces of good computing be with you!


      PowerShell & VBA Rule!
      Computer Specs

      • #1336274

        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.

    • #1336276

      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.

    • #1336426

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

    • #1336441

      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
          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.

      Paul Edstein
      [Fmr MS MVP - Word]

      • #1336451

        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.

        Dell E5570 Latitude, Intel Core i5 6440@2.60 GHz, 8.00 GB - Win 10 Pro

    Viewing 6 reply threads
    Reply To: Highlighting one word in a cell

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: