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.
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.
Above code is used to find the cell which contains value similar to FindItem.
Above code is used to combine two ranges into a single range.
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
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 firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.