Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: Highlighting words within a string if they appear on a list?

  1. #1
    New Member
    Join Date
    Jun 2017
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Highlighting words within a string if they appear on a list?

    Can someone help please?

    I have a list of boxes containing strings of text, I need to highlight any words appearing in these strings if they appear in my list... is this possible?

    For example:

    Sentence List
    My motorbike is yellow yellow
    My car is red red
    car

    Hope this makes sense any help appreciate

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,091
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Highlighting words within a string if they appear on a list?

    Try this:-
    Code:
    Sub MG14Jun53
    Dim Rng As Range, Dn As Range, R As Range
    Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
        For Each Dn In Rng.Offset(, -1)
            For Each R In Rng
                If InStr(Dn.Value, R.Value) > 0 Then
                    Dn.Characters(InStr(Dn.Value, R.Value), Len(R.Value)).Font.Color = vbRed
                End If
            Next R
    Next Dn
    End Sub
    Regards Mick

  3. #3
    New Member
    Join Date
    Jun 2017
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlighting words within a string if they appear on a list?

    Quote Originally Posted by MickG View Post
    Try this:-
    Code:
    Sub MG14Jun53
    Dim Rng As Range, Dn As Range, R As Range
    Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
        For Each Dn In Rng.Offset(, -1)
            For Each R In Rng
                If InStr(Dn.Value, R.Value) > 0 Then
                    Dn.Characters(InStr(Dn.Value, R.Value), Len(R.Value)).Font.Color = vbRed
                End If
            Next R
    Next Dn
    End Sub
    Regards Mick
    Hi Mike,

    Thanks so much!

    This worked but the only limitation i'm having is its case sensitive... for example it wont highlight if the word was 'Yellow' due to the caps or if it was partial such as 'yello'

    Is there anyway to get around this?

    Appreciate your help mate
    Last edited by BDexcel; Jun 14th, 2018 at 08:00 AM.

  4. #4
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,091
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Highlighting words within a string if they appear on a list?

    This will sort the "case sensitive" bit, but not the "Yello" ??
    Code:
    Sub MG14Jun08
    Dim Rng As Range, Dn As Range, R As Range
    Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
        For Each Dn In Rng.Offset(, -1)
            For Each R In Rng
                If InStr(1, Dn.Value, R.Value, vbTextCompare) > 0 Then
                    Dn.Characters(InStr(1, Dn.Value, R.Value, vbTextCompare), Len(R.Value)).Font.Color = vbRed
                End If
            Next R
    Next Dn
    End Sub
    Regards Mick

  5. #5
    New Member
    Join Date
    Jun 2017
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlighting words within a string if they appear on a list?

    Quote Originally Posted by MickG View Post
    This will sort the "case sensitive" bit, but not the "Yello" ??
    Code:
    Sub MG14Jun08
    Dim Rng As Range, Dn As Range, R As Range
    Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
        For Each Dn In Rng.Offset(, -1)
            For Each R In Rng
                If InStr(1, Dn.Value, R.Value, vbTextCompare) > 0 Then
                    Dn.Characters(InStr(1, Dn.Value, R.Value, vbTextCompare), Len(R.Value)).Font.Color = vbRed
                End If
            Next R
    Next Dn
    End Sub
    Regards Mick

    Thanks so much Mike! works great

    One last thing is it possible to add some kind of .Font.Bold = True so the results also bold?

    Appreciate your help with this

  6. #6
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,091
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Highlighting words within a string if they appear on a list?

    Try this:-
    Code:
    Sub MG14Jun58
    Dim Rng As Range, Dn As Range, R As Range
    Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
        For Each Dn In Rng.Offset(, -1)
            For Each R In Rng
                If InStr(1, Dn.Value, R.Value, vbTextCompare) > 0 Then
                    With Dn.Characters(InStr(1, Dn.Value, R.Value, vbTextCompare), Len(R.Value)).Font
                        .Color = vbRed
                        .Bold = True
                    End With
                End If
            Next R
    Next Dn
    End Sub
    Regards Mick

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,390
    Post Thanks / Like
    Mentioned
    77 Post(s)
    Tagged
    16 Thread(s)

    Default Re: Highlighting words within a string if they appear on a list?

    Comments
    - If you want "yello" to be highlighted as well as "yellow" you would need to include them both in the second column list (subject to my next point)
    - In your first post, you talked about "words" being highlighted. The suggested code does not account for "words" but simply text strings. For example, with your 3 'List' words in post 1, this happens would happen.
    Your scarring has reduced
    becomes
    Your scarring has reduced
    Perhaps that is what you want and you didn't mean "words", but if you did, see my alternate code below.
    - The suggested code only operates on as many rows in column A as there are in column B. That is, If 10 rows in column A and 3 rows in column B, the last 7 rows in col A will not be checked.
    - The suggested code only highlights the first occurrence of a string. For example
    My car is red but your car is blue
    becomes
    My car is red but your car is blue
    ie The second "car" is not highlighted

    My suggestion, to process all rows in column A and only look for "word" matches is
    Code:
    Sub HighlightWords()
      Dim RX As Object, Mtchs As Object
      Dim itm As Variant
      Dim c As Range
      
      
      Set RX = CreateObject("VBScript.RegExp")
      RX.Global = True
      RX.IgnoreCase = True
      RX.Pattern = "\b(" & Join(Application.Transpose(Range("B2", Range("B" & Rows.Count).End(xlUp)).Value), "|") & ")\b"
      Application.ScreenUpdating = False
      Columns("A").Font.ColorIndex = xlAutomatic
      Columns("A").Font.Bold = False
      For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        Set Mtchs = RX.Execute(c.Value)
        For Each itm In Mtchs
          With c.Characters(Start:=itm.firstindex + 1, Length:=itm.Length)
            .Font.Color = vbRed
            .Font.Bold = True
          End With
        Next itm
      Next c
      Application.ScreenUpdating = True
    End Sub
    Last edited by Peter_SSs; Jun 14th, 2018 at 09:43 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - 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

  8. #8
    New Member
    Join Date
    Jun 2017
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlighting words within a string if they appear on a list?

    Quote Originally Posted by Peter_SSs View Post
    Comments
    - If you want "yello" to be highlighted as well as "yellow" you would need to include them both in the second column list (subject to my next point)
    - In your first post, you talked about "words" being highlighted. The suggested code does not account for "words" but simply text strings. For example, with your 3 'List' words in post 1, this happens would happen.
    Your scarring has reduced
    becomes
    Your scarring has reduced
    Perhaps that is what you want and you didn't mean "words", but if you did, see my alternate code below.
    - The suggested code only operates on as many rows in column A as there are in column B. That is, If 10 rows in column A and 3 rows in column B, the last 7 rows in col A will not be checked.
    - The suggested code only highlights the first occurrence of a string. For example
    My car is red but your car is blue
    becomes
    My car is red but your car is blue
    ie The second "car" is not highlighted

    My suggestion, to process all rows in column A and only look for "word" matches is
    Code:
    Sub HighlightWords()
      Dim RX As Object, Mtchs As Object
      Dim itm As Variant
      Dim c As Range
      
      
      Set RX = CreateObject("VBScript.RegExp")
      RX.Global = True
      RX.IgnoreCase = True
      RX.Pattern = "\b(" & Join(Application.Transpose(Range("B2", Range("B" & Rows.Count).End(xlUp)).Value), "|") & ")\b"
      Application.ScreenUpdating = False
      Columns("A").Font.ColorIndex = xlAutomatic
      Columns("A").Font.Bold = False
      For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        Set Mtchs = RX.Execute(c.Value)
        For Each itm In Mtchs
          With c.Characters(Start:=itm.firstindex + 1, Length:=itm.Length)
            .Font.Color = vbRed
            .Font.Bold = True
          End With
        Next itm
      Next c
      Application.ScreenUpdating = True
    End Sub
    Thanks so much for the reply Peter, this works perfect for my needs

    Can i ask one thing? Is it possible to amend your code so it captures my partial searches also? As you advised it may give some false positives but currently it is missing some of the data i need captured.

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,390
    Post Thanks / Like
    Mentioned
    77 Post(s)
    Tagged
    16 Thread(s)

    Default Re: Highlighting words within a string if they appear on a list?

    Quote Originally Posted by BDexcel View Post
    Thanks so much for the reply Peter, this works perfect for my needs
    Great!


    Quote Originally Posted by BDexcel View Post
    Is it possible to amend your code so it captures my partial searches also? As you advised it may give some false positives but currently it is missing some of the data i need captured.
    I don't understand what you are wanting in relation to partial matches. Can you give some varied sample data (columns A & B) and the expected results like you did in post #1 that demonstrate what you want and how the current code misses some of the data you want? Any further explanation in relation to that sample data may also help clarify.

    BTW, best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only.
    Last edited by Peter_SSs; Jun 14th, 2018 at 09:32 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - 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
    Jun 2017
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlighting words within a string if they appear on a list?

    Thanks Peter,

    Appreciate the assistance and the advice.

    As I said the way you helped me with works so thanks so much! To make it absolutely perfect, it would be amazing if the code picked up 'partial' matches as demonstrated below:

    Sentence List
    There was one investigation Investigat
    This needed to be investigated Frustrat
    The customer was Frustrated
    There was Frustration from the staff

    Hope this makes more sense, if not possible its grand but if it was then this would make it perfect for my needs
    Last edited by BDexcel; Jun 15th, 2018 at 05:44 AM.

Some videos you may like

User Tag List

Tags for this Thread

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
  •