Creating macro for highlighing searches

[phiero21]

Hi, I've an excel file with multiple sheets and lots of data. I usually use Search (Ctrl + F) to find the data.

What I want is that If I search anything (Say TV), the searched (output) cell also becomes highlighted (e.g. the background of the cell containing the search string (TV) becomes Yellow").

Any help would be appeciated.

 

[kweaver]

A modification of a pretty click function from Ron de Bruin might be what you need.
You can prompt for the search string if you modify the MySearch line

Sub Color_cells_In_All_Sheets()
    Dim FirstAddress As String
    Dim MySearch As Variant
    Dim myColor As Variant
    Dim Rng As Range
    Dim I As Long
    Dim sh As Worksheet

    'Fill in the search Value and color Index
    MySearch = Array("DATA")
    myColor = Array("6")  ' this is the index for yellow.

    'You can also use more values in the Array
    'MySearch = Array("TV", "radio", "dish")
    'myColor = Array("3", "6", "10")  3 is red and 10 is green

    For Each sh In ActiveWorkbook.Worksheets

        'Fill in the Search range, for a range on each sheet
        'you can also use sh.Range("B1:D100")
        ' With sh.Cells
        With sh.Range("A1:Z100")
            'Change the fill color to "no fill" in all cells
            .Interior.ColorIndex = xlColorIndexNone
            ' .Interior.ColorIndex = 6
            For I = LBound(MySearch) To UBound(MySearch)

                'If you want to find exact match of the rng.value then use xlWhole
                'if you use LookIn:=xlValues it will also work with a
                'formula cell that evaluates to MySearch(I)
                Set Rng = .Find(What:=MySearch(I), _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlFormulas, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)

                If Not Rng Is Nothing Then
                    FirstAddress = Rng.Address
                    Do
                        Rng.Interior.ColorIndex = myColor(I)
                        'Rng.Interior.ColorIndex = 6
                        Set Rng = .FindNext(Rng)
                    Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
                End If
            Next I
        End With
    Next sh
End Sub

 

[kevin9999]

Slightly simpler version using an input box for the string, highlighting any cell that contains the inputted string anywhere within the cell.

Option Explicit
Sub phiero21()
    Dim ws As Worksheet, s As String
    With Application.ReplaceFormat
        .Interior.Color = vbYellow
    End With
    
    s = InputBox("type the text you want to highlight")
    For Each ws In ThisWorkbook.Worksheets
        With ws.UsedRange
            .Interior.Color = xlNone
            .Replace s, s, ReplaceFormat:=True
        End With
    Next ws
End Sub

 

[phiero21]

Thanks both kwearer & kevin9999

 

[kevin9999]

Glad we were able to help, and thanks for the feedback

 

[Akuini]

@phiero21
I just answered a similar question in another thread.
I wrote a macro to find and highlight data via a userform.
The code uses Conditional Formatting instead of changing cell interior color, so it won't affect any cell's interior color.