Upcoming Power Excel Seminars
Thanks Thanks:  0
Page 1 of 8 123 ... LastLast
Results 1 to 10 of 72

Thread: Conditional Formatting: Highlight only certain words in cell (text)?

  1. #1
    New Member
    Join Date
    Oct 2009
    Location
    Germany
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional Formatting: Highlight only certain words in cell (text)?

    Hi to all,

    I know that you can highlight a cell or it's entire contents with a certain color, but can you highlight only certain portions of it's contents using Conditional Formatting?

    A simple example: If cell A1 contains the text "The pink elephant has blue and green spots." and cell B1 contains the text "blue", using cell B1 as a reference, is there a way to highlight only the text "blue" in A1 with the color blue using Conditional Formatting?

    Thanks in advance for any answer to this.


    Marc.

  2. #2
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,654
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Hello Marc,

    I think even in Excel 2007 it is still an all or nothing proposition. You can change the Font Color of the individual letters using VBA.

    Sincerely,
    Leith Ross

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,153
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    You cannot do that with Conditional Formatting, or upon values returned by formulas.

    If the string in cell A1 is a constant, such as if you typed in or pasted
    "The pink elephant has blue and green spots"
    and you want only "blue" (as entered in cell B1 for that purpose), you'd need VBA, example:

    Code:
    Sub Test1()
    Dim strString$, x&
    strString = Range("B1").Value
    With Range("A1")
    .Font.ColorIndex = 1
    For x = 1 To Len(.Text) - Len(strString) Step 1
    If Mid(.Text, x, Len(strString)) = strString Then .Characters(x, Len(strString)).Font.ColorIndex = 5
    Next x
    End With
    End Sub
    Note, if the string is
    "The pink elephant has blue and green spots and is haunted by Bluebeard's ghost"
    then the string would have both "blue" entries turn blue, example

    "The pink elephant has blue and green spots and is haunted by Bluebeard's ghost"

  4. #4
    New Member
    Join Date
    Oct 2009
    Location
    Germany
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Thank you both, Leith and Tom for your replies.

    Yeah, I wasn't sure if I missed something in the help files for Excel or not.

    I was hoping that this could be done without "resorting" to VBA and keep things as simple as possible.

    Tom, thanks for the time and work you spent on the VBA solution. I will keep this in mind, should I decide to the extra mile and use VBA in my sheet.


    This site and all it's helpful and gifted members rock!

    Thanks again guys.

  5. #5
    New Member
    Join Date
    Nov 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Hi everyone,

    I find the VBA code provided by Tom Urtis very useful. I tried to extend this code in VBA, so that this rule will be applied in my whole sheet, but unfortunately I cannot make it work. Could you please provide me with advice?

    Thanks in advance!

  6. #6
    New Member
    Join Date
    Nov 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Case solved. A colleague of mine simply introduced the offset function into the code:

    Sub Test1()
    Dim strString$, x&
    For i = 0 To 3130
    strString = Range("B1").Offset(i, 0).Value
    With Range("A1").Offset(i, 0)
    .Font.ColorIndex = 1
    For x = 1 To Len(.Text) - Len(strString) Step 1
    If Mid(.Text, x, Len(strString)) = strString Then .Characters(x, Len(strString)).Font.ColorIndex = 5
    Next x
    End With
    Next i

    End Sub
    Nice forum though. Think I'm going to check this more often!

  7. #7
    New Member
    Join Date
    Feb 2015
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Guys - I know it was too hard to think about this solution but here you go with the solution to highlight certain word in cell
    1) Open Excel Sheet
    2) Insert Text Box
    3) Move the text box to the word you want to highlight it
    4) remove text box outline
    5) fill the box color as you wish
    6) reduce the box transparency so that your highlighted word appears
    7) Copy and past the box to the other words you wish to highlight having the text box size modified according to the word length and so on....

    Your feedback really matters!
    BR,
    Mohamed Hassaan From Egypt
    Last edited by m_hassaan; Feb 21st, 2015 at 05:32 PM.

  8. #8
    New Member
    Join Date
    Aug 2015
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Quote Originally Posted by Tom Urtis View Post
    You cannot do that with Conditional Formatting, or upon values returned by formulas.

    If the string in cell A1 is a constant, such as if you typed in or pasted
    "The pink elephant has blue and green spots"
    and you want only "blue" (as entered in cell B1 for that purpose), you'd need VBA, example:

    Code:
    Sub Test1()
    Dim strString$, x&
    strString = Range("B1").Value
    With Range("A1")
    .Font.ColorIndex = 1
    For x = 1 To Len(.Text) - Len(strString) Step 1
    If Mid(.Text, x, Len(strString)) = strString Then .Characters(x, Len(strString)).Font.ColorIndex = 5
    Next x
    End With
    End Sub
    Note, if the string is
    "The pink elephant has blue and green spots and is haunted by Bluebeard's ghost"
    then the string would have both "blue" entries turn blue, example

    "The pink elephant has blue and green spots and is haunted by Bluebeard's ghost"
    Hi Tom,
    This code is perfect for what I am trying to do and working really well.
    I know nothing about VBA but am amazing at copying and pasting code from the internet.... Could you help me convert the cell references to ranges? As in, I don't just want to look in cell "B1" but rather the entire column G:G (tried "G:G" but it didn't like it!)
    Thanks
    Hannah

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    37,183
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Hannah

    Tom doesn't seem to be about as much as he used to be, so I'll try a modification for you to test.
    Note that Tom's code is case-sensitive.
    It still uses cell B1 to get the string to be identified in the column G cells.
    Test on a set of data that is not too big to start with,

    Code:
    Sub Test1()
      Dim strString$, x&
      Dim rngCell As Range
      
      strString = Range("B1").Value
      Application.ScreenUpdating = False
      For Each rngCell In Range("G1", Range("G" & Rows.Count).End(xlUp))
        With rngCell
          .Font.ColorIndex = 1
          For x = 1 To Len(.Text) - Len(strString) Step 1
            If Mid(.Text, x, Len(strString)) = strString Then .Characters(x, Len(strString)).Font.ColorIndex = 5
          Next x
        End With
      Next rngCell
      Application.ScreenUpdating = True
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    New Member
    Join Date
    Aug 2015
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Quote Originally Posted by Peter_SSs View Post
    Hannah

    Tom doesn't seem to be about as much as he used to be, so I'll try a modification for you to test.
    Note that Tom's code is case-sensitive.
    It still uses cell B1 to get the string to be identified in the column G cells.
    Test on a set of data that is not too big to start with,

    Code:
    Sub Test1()
      Dim strString$, x&
      Dim rngCell As Range
      
      strString = Range("B1").Value
      Application.ScreenUpdating = False
      For Each rngCell In Range("G1", Range("G" & Rows.Count).End(xlUp))
        With rngCell
          .Font.ColorIndex = 1
          For x = 1 To Len(.Text) - Len(strString) Step 1
            If Mid(.Text, x, Len(strString)) = strString Then .Characters(x, Len(strString)).Font.ColorIndex = 5
          Next x
        End With
      Next rngCell
      Application.ScreenUpdating = True
    End Sub
    Works PEFECTLY! Thank you

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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