Partial font color change in text of a listbox userform VBA when matches criteria

[transcend]

Hi everyone,

I'm new with VBA excel and trying to create a dynamic search userform using a textbox and list box. 2 parts to this:

1) When user enters text in userform textbox, it should search the database ( rows and 5 columns), and return the values in list box (with 5 columns in this case) when the text entered in textbox matches even partially from any of the 5 columns.

2) It should also change the text matched to red. (even if matches to multiple columns in the same row)

So far I have been able to do the 1st Part but need help for the 2nd part of it??
Any help is appreciated.


The code used so far is this:

' Start*******

Private Sub UserForm_Initialize()
    
    Me.TextBox1.SetFocus
    
End Sub


Private Sub TextBox1_Change()


    Dim i As Long
    Dim SearchVal As String
        
    On Error Resume Next
        
        Me.TextBox1.Text = UCase(Me.TextBox1.Text)
        Me.ListBox1.ColumnWidths = "120;120;76;174;186"
        Me.ListBox1.Clear
    
    If Me.TextBox1.Text <> "" Then
    
        For i = 2 To Worksheets("DataBase").Cells(Rows.Count, 1).End(xlUp).Row
            
            If InStr((Worksheets("DataBase").Cells(i, 1).Value), SearchVal) > 0 Or InStr((Worksheets("DataBase").Cells(i, 2).Value), SearchVal) > 0 Or InStr((Worksheets("DataBase").Cells(i, 3).Value), SearchVal) > 0 Or InStr((Worksheets("DataBase").Cells(i, 4).Value), SearchVal) > 0 Or InStr((Worksheets("DataBase").Cells(i, 5).Value), SearchVal) > 0 Then
                
                Me.ListBox1.AddItem Worksheets("DataBase").Cells(i, 1).Value
                
                For c = 1 To Me.ListBox1.ColumnCount
                   Me.ListBox1.List(ListBox1.ListCount - 1, c) = Worksheets("DataBase").Cells(i, c + 1).Value
                Next c
                
            End If
        Next i
    End If
    
End Sub

' End*******

https://drive.google.com/open?id=1c5J_lalrvmV07a5jcbzpOxvCCMDV3dlG
https://drive.google.com/file/d/1c5J_lalrvmV07a5jcbzpOxvCCMDV3dlG/view?usp=sharing