Highlighting Unique List of Words in Each Cell of a Selection of Cells - Excel VBA

0 votes

Description

I feel like the solution is so obvious, but I just can't grasp it. Using a Userform input and string splitting, I'm creating code to highlight a list of defined terms in each cell of a selection. This code was altered from one I discovered elsewhere in the public domain. Userform and capitalization functions in the Module were not included in the original code. The changes I made to the code to make it non-cap-sensitive worked properly before I included the Userform component. As far as I can determine, the problem appears to be caused by the Module rather than the Userform. It always uses the last word in a list, which is a persistent problem. 

The code used and examples of its application are provided below. Any help would be greatly appreciated!

(Ex. 1) Data to be altered: enter image description here

(Ex. 2) Blank Userform: enter image description here

(Ex. 3) Filled Userform: enter image description here

(Ex. 4) Data altered: enter image description here

*Note: The Scroll Bar in the Userform is currently not implemented.

Module: Mod2HighlightString

    'Updateby Extendoffice
    Application.ScreenUpdating = False
    Dim Rng As Range '-variable to hold each cells value in the selection
    Dim cFnd As String '-variable that holds the user input from the userform
    Dim xTmp As String '-variable for temporary holds on parts of string (I think)
    Dim i As Long '-variable for holding color index value
    Dim j As Variant '-variable for testing a split array
    Dim k As Integer '-variable for a loop
    Dim x As Long '-variable for a loop
    Dim m As Long '-variable for holding number of times a word is in a cell
    Dim y As Long '-variable for holding len function
    Dim Color As String '-variable to hold value provided for desired font color
    Dim xFNum As Integer '-variable for a loop
    Dim xArrFnd As Variant '-variable holds array of words to search for provided from userform
    Dim xStr As String '-variable that temp holds a single string from the array of strings
    Mod2User.Show
    Color = CStr(Mod2User.ComboBox1.Value)
    If Color = "Red" Then i = 3
    If Color = "Green" Then i = 4
    If Color = "Blue" Then i = 5
    If Color = "Cyan" Then i = 8
    If Color = "Pink" Then i = 7
    If Color = "Orange" Then i = 46
    cFnd = CStr(Mod2User.TextBox1.Value) 'InputBox("Please enter the text, separate them by comma:")
    Debug.Print Color; Chr(10); cFnd
    If Len(cFnd) < 1 Then Exit Sub
    'xArrFnd - holds array of words to search for
    xArrFnd = Split(cFnd, Chr(10))
'    j = UBound(xArrFnd)
    
    For Each Rng In Selection
        With Rng
            'rng.value will supply the cells content within the selection
'            Debug.Print .Value
            For xFNum = 0 To UBound(xArrFnd)
                'xStr - Temp holds a single string from the array of strings
                xStr = xArrFnd(xFNum)
                y = Len(xStr)
                m = UBound(Split(UCase(Rng.Value), UCase(xStr)))
                
                j = Split(UCase(Rng.Value), UCase(xStr))
                
                Debug.Print "word "; xFNum; " is "; xStr
                Debug.Print "y:"; y; " m: "; m
                Debug.Print "Split: ["; UCase(Rng.Value); "], using: ["; UCase(xStr); "]"
                
                For k = 0 To UBound(j)
                    Debug.Print "Result: "; j(k)
                Next k
                
                If m > 0 Then
                    xTmp = ""
                    For x = 0 To m - 1
                        xTmp = xTmp & Split(UCase(Rng.Value), UCase(xStr))(x)
                        Debug.Print UCase(xStr)
                        Debug.Print UCase(Rng.Value)
                        
'                        Debug.Print "at x ="; x; "first xtmp = "; xTmp
                        .Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = i
                        xTmp = xTmp & xStr
                    Next
                End If
            Next xFNum
        End With
    Next Rng
    Unload Mod2User
    Application.ScreenUpdating = True
End Sub

Userform: Mod2User

Private m_Cancelled As Boolean

Public Property Get Cancelled() As Variant
    Cancelled = m_Cancelled
End Property

Private Sub ComboBox1_Change()

End Sub

Private Sub Label1_Click()

End Sub

Private Sub ScrollBar1_Change()

End Sub

Private Sub TextBox1_Change()
    
End Sub

Private Sub UserForm_Click()

End Sub



Private Sub CommandButton1_Click()
    Hide
End Sub

Private Sub UserForm_Initialize()

    With Mod2User
      .Width = Application.Width * 0.293
      .Height = Application.Height * 0.35
    End With
    
    
    With ComboBox1
        .Clear
        .AddItem "Red"
        .AddItem "Green"
        .AddItem "Blue"
        .AddItem "Cyan"
        .AddItem "Pink"
        .AddItem "Orange"
    End With
    
    TextBox1.MultiLine = True
'    TextBox1.ScrollBars =

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer _
                                        , CloseMode As Integer)
    
    ' Prevent the form being unloaded
    If CloseMode = vbFormControlMenu Then Cancel = True
    
    ' Hide the Userform and set cancelled to true
    Hide
    m_Cancelled = True
    
End Sub

Function GetComboBox1() As String
    GetComboBox1 = CStr(ComboBox1.Value)
End Function

Debug.Print Results

Blue
the
downey
fierce
word  0  is the

y: 4  m:  0 
Split: [THE CAT GOES TO THE MOON ON A FINE SUNDAY AFTERNOON IF THE BAKER IS IN THE ], using: [THE
]
Result: THE CAT GOES TO THE MOON ON A FINE SUNDAY AFTERNOON IF THE BAKER IS IN THE 
word  1  is downey

y: 7  m:  0 
Split: [THE CAT GOES TO THE MOON ON A FINE SUNDAY AFTERNOON IF THE BAKER IS IN THE ], using: [DOWNEY
]
Result: THE CAT GOES TO THE MOON ON A FINE SUNDAY AFTERNOON IF THE BAKER IS IN THE 
word  2  is fierce
y: 6  m:  0 
Split: [THE CAT GOES TO THE MOON ON A FINE SUNDAY AFTERNOON IF THE BAKER IS IN THE ], using: [FIERCE]
Result: THE CAT GOES TO THE MOON ON A FINE SUNDAY AFTERNOON IF THE BAKER IS IN THE 
word  0  is the

y: 4  m:  0 
Split: [ROBERT DOWNEY JUNIOR GOES TO MEMPHIS TO PLAY FOOTBALL], using: [THE
]
Result: ROBERT DOWNEY JUNIOR GOES TO MEMPHIS TO PLAY FOOTBALL
word  1  is downey

y: 7  m:  0 
Split: [ROBERT DOWNEY JUNIOR GOES TO MEMPHIS TO PLAY FOOTBALL], using: [DOWNEY
]
Result: ROBERT DOWNEY JUNIOR GOES TO MEMPHIS TO PLAY FOOTBALL
word  2  is fierce
y: 6  m:  0 
Split: [ROBERT DOWNEY JUNIOR GOES TO MEMPHIS TO PLAY FOOTBALL], using: [FIERCE]
Result: ROBERT DOWNEY JUNIOR GOES TO MEMPHIS TO PLAY FOOTBALL
word  0  is the

y: 4  m:  0 
Split: [THE OOMPA LOOPAS WERE FIERCE FIGHTERS], using: [THE
]
Result: THE OOMPA LOOPAS WERE FIERCE FIGHTERS
word  1  is downey

y: 7  m:  0 
Split: [THE OOMPA LOOPAS WERE FIERCE FIGHTERS], using: [DOWNEY
]
Result: THE OOMPA LOOPAS WERE FIERCE FIGHTERS
word  2  is fierce
y: 6  m:  1 
Split: [THE OOMPA LOOPAS WERE FIERCE FIGHTERS], using: [FIERCE]
Result: THE OOMPA LOOPAS WERE 
Result:  FIGHTERS
FIERCE
THE OOMPA LOOPAS WERE FIERCE FIGHTERS
word  0  is the

y: 4  m:  0 
Split: [THE DOG HAS A A FIERCE PERSONALITY AND ONLY GOES NUMBER 2 ON THE NEIGHBORS YARD], using: [THE
]
Result: THE DOG HAS A A FIERCE PERSONALITY AND ONLY GOES NUMBER 2 ON THE NEIGHBORS YARD
word  1  is downey

y: 7  m:  0 
Split: [THE DOG HAS A A FIERCE PERSONALITY AND ONLY GOES NUMBER 2 ON THE NEIGHBORS YARD], using: [DOWNEY
]
Result: THE DOG HAS A A FIERCE PERSONALITY AND ONLY GOES NUMBER 2 ON THE NEIGHBORS YARD
word  2  is fierce
y: 6  m:  1 
Split: [THE DOG HAS A A FIERCE PERSONALITY AND ONLY GOES NUMBER 2 ON THE NEIGHBORS YARD], using: [FIERCE]
Result: THE DOG HAS A A 
Result:  PERSONALITY AND ONLY GOES NUMBER 2 ON THE NEIGHBORS YARD
FIERCE
THE DOG HAS A A FIERCE PERSONALITY AND ONLY GOES NUMBER 2 ON THE NEIGHBORS YARD
word  0  is the

y: 4  m:  0 
Split: [CATS SECRETLY LOVE DOGS BUT CANT OWN UP TO IT], using: [THE
]
Result: CATS SECRETLY LOVE DOGS BUT CANT OWN UP TO IT
word  1  is downey

y: 7  m:  0 
Split: [CATS SECRETLY LOVE DOGS BUT CANT OWN UP TO IT], using: [DOWNEY
]
Result: CATS SECRETLY LOVE DOGS BUT CANT OWN UP TO IT
word  2  is fierce
y: 6  m:  0 
Split: [CATS SECRETLY LOVE DOGS BUT CANT OWN UP TO IT], using: [FIERCE]
Result: CATS SECRETLY LOVE DOGS BUT CANT OWN UP TO IT
word  0  is the

y: 4  m:  0 
Split: [CHUCK NORRIS IS A PERSON LIKE YOU AND I], using: [THE
]
Result: CHUCK NORRIS IS A PERSON LIKE YOU AND I
word  1  is downey

y: 7  m:  0 
Split: [CHUCK NORRIS IS A PERSON LIKE YOU AND I], using: [DOWNEY
]
Result: CHUCK NORRIS IS A PERSON LIKE YOU AND I
word  2  is fierce
y: 6  m:  0 
Split: [CHUCK NORRIS IS A PERSON LIKE YOU AND I], using: [FIERCE]
Result: CHUCK NORRIS IS A PERSON LIKE YOU AND I
word  0  is the

y: 4  m:  0 
Split: [HARLM SHAKE WAS A VIBE], using: [THE
]
Result: HARLM SHAKE WAS A VIBE
word  1  is downey

y: 7  m:  0 
Split: [HARLM SHAKE WAS A VIBE], using: [DOWNEY
]
Result: HARLM SHAKE WAS A VIBE
word  2  is fierce
y: 6  m:  0 
Split: [HARLM SHAKE WAS A VIBE], using: [FIERCE]
Result: HARLM SHAKE WAS A VIBE
word  0  is the

y: 4  m:  0 
Split: [WHEN I GO TO FRANCE I WILL EAT SNAIL], using: [THE
]
Result: WHEN I GO TO FRANCE I WILL EAT SNAIL
word  1  is downey

y: 7  m:  0 
Split: [WHEN I GO TO FRANCE I WILL EAT SNAIL], using: [DOWNEY
]
Result: WHEN I GO TO FRANCE I WILL EAT SNAIL
word  2  is fierce
y: 6  m:  0 
Split: [WHEN I GO TO FRANCE I WILL EAT SNAIL], using: [FIERCE]
Result: WHEN I GO TO FRANCE I WILL EAT SNAI
Jan 12, 2023 in Others by Kithuzzz
• 38,000 points
531 views

1 answer to this question.

0 votes

In a Textbox it is a vbcrlf not chr(10)

xArrFnd = Split(cFnd, Chr(10))

should be

xArrFnd = Split(cFnd, vbCrLf)

Your split works but contains for each word still a chr(13)

answered Jan 12, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

How to apply zoom animation for each element of a list in angular?

Hey @Sid, do check if this link ...READ MORE

answered Jul 30, 2019 in Others by Vardhan
• 13,150 points
1,590 views
0 votes
1 answer

Generate a flat list of all excel cell formulas

Hello, you'll have to follow certain steps ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
565 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,600 points
3,756 views
0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,600 points
726 views
0 votes
1 answer

Split and group values in excel

variant using scripting.dictionary: Sub test() Dim ...READ MORE

answered Jan 22, 2023 in Others by narikkadan
• 63,600 points
606 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,258 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,696 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
969 views
0 votes
1 answer

Getting data out of a cell with a #NAME? error in Excel VBA

If you need VBA, use .Formula: Dim f As ...READ MORE

answered Feb 3, 2023 in Others by narikkadan
• 63,600 points
670 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP