Find method of Range object

In this article, we will use find method of Range object to highlight the cell which contains the value similar to search words.

Raw data for this example consists of company name, employee id and employee name. We have raw data from different companies.

In this example, we want to find and highlight the cell which match with particular defined company name in cell I8.

To highlight the cells with particular company names, enter the company name in cell I8 and click on the “Submit” button. “Submit” button is assigned “HighlightMatchingResult” macro.

“HighlightMatchingResult” macro will highlight the cell which contains the matching company name with Yellow color.

Logic explanation

In this example, we have created a custom function “FindRange” and a macro “HighlightMatchingResult”.

Custom function “FindRange” will create a range with all the cells that contain value similar to the searched company name.

“HighlightMatchingResult” macro will call the custom function and highlight the range returned by the custom function in Yellow color.

Code explanation

SearchRange.Find(What:=FindItem)

Above code is used to find the cell which contains value similar to FindItem.

Union(FindRange, MatchingRange)

Above code is used to combine two ranges into a single range.

SearchRange.FindNext(MatchingRange)

Above code is used to find the next cell which contains value similar to FindItem.

Please follow below for the code

Option Explicit

Function FindRange(FindItem As Variant, SearchRange As Range) As Range

'Declaring variables
Dim MatchingRange As Range
Dim FirstAddress As String

With SearchRange
    
    'Finding the range whose value match with FindItem
    Set MatchingRange = .Find(What:=FindItem)
    
    'Checking whether any match exist
    If Not MatchingRange Is Nothing Then
    
        Set FindRange = MatchingRange
        'Getting the address of first matching range
        FirstAddress = MatchingRange.Address
        
        Do
            'Union of all the ranges whose value match with FindItem
            Set FindRange = Union(FindRange, MatchingRange)
            'Finding the next range whose value match with FindItem
            Set MatchingRange = .FindNext(MatchingRange)
        Loop While MatchingRange.Address <> FirstAddress
    End If
    
End With
End Function

Sub HighlightMatchingResult()

'Declaring variables
Dim MappingRange As Range
Dim UserInput As String

'Getting value input by user from cell I8
UserInput = Range("I8").Value

'Calling FindRange custom function
Set MappingRange = FindRange(UserInput, ActiveSheet.Columns("A"))

'Highlighting the mapped range with Yellow color
MappingRange.Interior.Color = RGB(255, 255, 0)

End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com