Results 1 to 10 of 10
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    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.
    JimmyW
    Helena, MT

  2. #2
    4 Star Lounger
    Join Date
    May 2012
    Posts
    404
    Thanks
    0
    Thanked 49 Times in 39 Posts
    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/

  3. #3
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Thanks. I got a copy, but I don't see where it does what I want, or even do a find/replace.
    JimmyW
    Helena, MT

  4. #4
    4 Star Lounger
    Join Date
    May 2012
    Posts
    404
    Thanks
    0
    Thanked 49 Times in 39 Posts
    There's a macro on this site which might do the job instead: http://www.ozgrid.com/forum/showthread.php?t=66197

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jimmy,

    Here's the code from the link provided by Xircal adapted to do color:
    Code:
    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.
    Last edited by RetiredGeek; 2012-06-10 at 07:52.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    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.
    Attached Files Attached Files
    JimmyW
    Helena, MT

  7. #7
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    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.
    JimmyW
    Helena, MT

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I'm curious about what the scenario is that requires this solution?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    I too am curious. Be that as it may, try:
    Code:
    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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    3 Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, UK
    Posts
    237
    Thanks
    24
    Thanked 50 Times in 22 Posts
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •