CODEWITHSUNDEEP
excelvbadynamicfilteringhighlight
llorcs
llorcs

Reputation: 79

Dynamic Search with highlight - Excel VBA

I would like to achieve the following: In my excel sheet I have a set of data to which I've applied dynamic filtering by creating a "Search box". The filtering itself works okay, no problems there, however, I would like to further improve it, by highlighting the text (that is entered into the search box) in the filtered rows in red. I am attaching a screenshot of what I would like to see in the final version.

enter image description here

Any idea how this can be entered into my current code?

As always, any help is greatly appreciated! Thank you!

Below is the code I use for the dynamic filtering:

Private Sub TextBox1_Change()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


If Len(TextBox1.Value) = 0 Then
    Sheet1.AutoFilterMode = False
Else
    If Sheet1.AutoFilterMode = True Then
        Sheet1.AutoFilterMode = False
        End If

    Sheet1.Range("B4:C" & Rows.Count).AutoFilter field:=1, Criteria1:="*" & TextBox1.Value & "*"

    End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Upvotes: 2

Views: 851

Answers (1)

Vityata
Vityata

Reputation: 43585

Consider something like this - Write in a worksheet the following:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If Target <> Range("a1") Then Exit Sub
    SelectAndChange (Target)

End Sub

Private Sub SelectAndChange(strValue As String)

    Dim rngCell     As Range
    Dim rngRange    As Range
    Dim strLookFor  As String
    Dim arrChar     As Variant
    Dim lngCounter  As Long

    If strValue = vbNullString Then Exit Sub

    Application.EnableEvents = False

    Set rngRange = Range("E1:E10")
    rngRange.Font.Color = vbBlack
    strLookFor = Range("A1").Value

    For Each rngCell In rngRange
        For lngCounter = 1 To Len(rngCell) - Len(strLookFor) + 1
            If Mid(rngCell, lngCounter, Len(strLookFor)) = strLookFor Then
                rngCell.Characters(lngCounter, Len(strLookFor)).Font.Color = vbRed
            End If
        Next lngCounter
    Next rngCell

    Application.EnableEvents = True

End Sub

The values in E1:E10 would be dependent from the value in A1 like this:

enter image description here

Upvotes: 1

Related Questions