Consulting

Results 1 to 14 of 14

Thread: [VBA] find + highlight multiple words

  1. #1

    [VBA] find + highlight multiple words

    Hi, the code is written for a button on a Userform.
    When clicked, it prompts the user to input words to be searched,
    e.g. if "text1,text2,text3" are inputted,
    the following code would start to search within the selected range and color the corresponding inputted words, "text1" , "text2", "text3" with a certain color.
    However, it only succeeds in coloring the first word "text1" but not the rest.
    What needs to be changed?
    Thanks.


    Private Sub oYellow_Click()   
     Call CrHL(tTxtCr.Value, wdYellow, wdColorYellow)
    End Sub
    
    '   [ CrHL ]
    Sub CrHL(ByVal f As String, _
             ByVal HL As Long, _
             ByVal Cr As Long)
        Dim A As Variant
        Dim rng As Range
        Application.ScreenUpdating = False
        '-------------------
        A = Split(f, ",")
        Set rng = Selection.Range
        '-------------------
        With rng
            For i = LBound(A) To UBound(A) And .InRange(Selection.Range)
                While .Find.Execute(A(i))
                    '   Cr
                    Select Case ckFontCr
                        Case True: .Font.Color = Cr
                        Case False: .HighlightColorIndex = HL
                    End Select
                Wend
            Next
        End With
        '-------------------
        Unload Me
    End Sub

  2. #2
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Your code doesn't compile. Why bother declaring some variables if you are not going to declare all of them. What is ckfontCr?

    You have to reset the range with each loop:

    Private Sub oYellow_Click()
        Call CrHL("test,boy", wdYellow, wdColorYellow)
    End Sub
     
     '   [ CrHL ]
    Sub CrHL(ByVal f As String, _
        ByVal HL As Long, _
        ByVal Cr As Long)
        Dim A As Variant
        Dim rng As Range
        Dim i, ckFontCr
        Dim oRng As Range
        Application.ScreenUpdating = False
         '-------------------
        A = Split(f, ",")
        'Set oRng = Selection.Range.Duplicate
        For i = LBound(A) To UBound(A)
          Set rng = Selection.Range.Duplicate
         '-------------------
        With rng
        
                While .Find.Execute(A(i)) And .InRange(rng)
                     '   Cr
                    Select Case ckFontCr
                    Case True: .Font.Color = Cr
                    Case False: .HighlightColorIndex = HL
                    End Select
                Wend
            
        End With
        Next
        'End With '-------------------
        'Unload Me
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

  3. #3
    VBAX Tutor
    Joined
    Jul 2016
    Posts
    266
    Location
    2 questions.

    1. Is this macro supposed to change "test" and "Boy" yellow? For me only "test" is changing.
    2. Is it possible to define the search words each time using an input box?

    Thanks

  4. #4
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    No it is supposed to highlight "test" and "boy" (not Boy) and it does here if test and boy are in the selected range.

    Yes it is possible. Replace "test,boy" with InputBox("Enter words to find separated with a comma")
    Greg

    Visit my website: http://gregmaxey.com

  5. #5
    VBAX Tutor
    Joined
    Jul 2016
    Posts
    266
    Location
    TEST - Copy.docm

    Thanks Greg. When I run this macro it highlights TEST, Test, test but does not highlight BOY, Boy, boy. I added a line for an input box for another test. Both only seem to find the first word entered.

    When I enter "test, boy" it highlights all variations of test only.
    When I enter "boy, test" it highlights all variations of boy only.

  6. #6
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Quote Originally Posted by Kilroy View Post
    TEST - Copy.docm

    Thanks Greg. When I run this macro it highlights TEST, Test, test but does not highlight BOY, Boy, boy. I added a line for an input box for another test. Both only seem to find the first word entered.

    When I enter "test, boy" it highlights all variations of test only.
    When I enter "boy, test" it highlights all variations of boy only.
    I didn't test for Test or Boy. I'll look at your attachment when I get home tonigjt
    Greg

    Visit my website: http://gregmaxey.com

  7. #7
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Well it hightlights nothing when you click your command button because there is no text selected. Here, select ting the text highlights all variations of test and boy. If you add a msgbox as show does it return "1 test" of the first pass and "1 boy" on the second pass

    For i = LBound(A) To UBound(A)
    MsgBox UBound(A) & " " & A(i)
    Greg

    Visit my website: http://gregmaxey.com

  8. #8
    VBAX Tutor
    Joined
    Jul 2016
    Posts
    266
    Location
    TEST - Copyr1.docm

    Good morning Greg and thanks for the replies.
    1. I do not need to select any text for this to work but the placement of the cursor must be on the top of the page.
    2. When I get rid of the button and the popups it finds all words and highlights them
    3. When a table is introduced it highlights the first word searched (regardless of the row found in) but does not look in the table for the rest of the words.
    4. When used in a document with only a table it works great.

    Am I doing something wrong?
    Last edited by Kilroy; 06-07-2017 at 06:32 AM.

  9. #9
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    1. That is because when you use "Selection" and the selection is the insertion point then the selection scope is automatically from the IP to the end of the document main text storyrange.
    2. When you "click" the button in the documents then the selection is defined and the button range.


    Change to:
    Set rng = ActiveDocument.Range 'Selection.Range.Duplicate
    Greg

    Visit my website: http://gregmaxey.com

  10. #10
    VBAX Tutor
    Joined
    Jul 2016
    Posts
    266
    Location
    Thanks Greg. Any idea why when there is a table in the document it only looks for the first word?

  11. #11
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Are you typing "test,boy,warm"
    or"test, boy, warm"

    Type the first
    Last edited by gmaxey; 06-09-2017 at 01:30 PM.
    Greg

    Visit my website: http://gregmaxey.com

  12. #12
    VBAX Tutor
    Joined
    Jul 2016
    Posts
    266
    Location
    Works perfectly. Taking out the space after the comma was the problem. good find Greg I never would have thought to take them out.

    Sorry smallxyz I didn't mean to hijack your post.

  13. #13
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    It works with test,boy,warm because you are searching for "test" "boy" "warm"
    If you type test, boy, warm then you are searching for "test" " boy" " warm" and " boy" " warm" are not found in the table.
    Greg

    Visit my website: http://gregmaxey.com

  14. #14
    VBAX Tutor
    Joined
    Jul 2016
    Posts
    266
    Location
    Thanks Greg

Posting Permissions

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