How To Find method of Range object in Excel VBA?
Tags: tofindmethodofrangeo, vbamacros
Published: 2021-09-28 04:30:12

---
title: How To Find method of Range object in Excel VBA?
published: true
date: 2021-09-23 07:59:32 UTC
tags: ToFindMethodOfRangeO,VBAMacros
canonical_url: https://geekexcel.com/how-to-find-method-of-range-object-in-excel-vba/
---

In this tutorial, we will guide you on how to ** find the method of a range object** in Excel VBA. Let’s get them below!! Get an official version of MS Excel from the following link: **[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)**

## Example

- Firstly, you need to  **create a sample data** consists of **company name, employee id and employee name**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Sample-data-36.png)<figcaption>Sample data</figcaption>

- In the Excel Worksheet, you have to go to the  **Developer Tab.**
- Then, you need to ** ** select the  **Visual Basic**  option under the  **Code**  section.

![Select Visual Basic](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

- Now, you have to  **copy and paste the code**  given below.

```
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

```

- You need to  **save the code**  by selecting it and then  **close the window**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Save-the-Code-73.png)<figcaption>Save the Code</figcaption>

- Again, you have to go to the  **Excel Spreadsheet** , and click on the  **Developer Tab**.
- You need to choose the  **Macros option**  in the Code section.

![Choose Macro option](https://geekexcel.com/wp-content/uploads/2021/09/Choose-Macro-option-11-1024x184.png)<figcaption>Choose Macro option</figcaption>

- Then, you have to make sure that your  **macro name is selected**  and click the  **Run ** button.

![](https://geekexcel.com/wp-content/uploads/2021/09/Run-the-Code-43.png)<figcaption>Run the Code</figcaption>

- To **highlight the cells with particular company names** , you need to **enter the company name** in **cell F8** and click on the **Submit** button.

![](https://geekexcel.com/wp-content/uploads/2021/09/Enter-the-CompanyName-1024x432.png)<figcaption>Enter the Company Name</figcaption>

- Finally, you will get the **output** as **highlighted text** as given below in **Microsoft Excel**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Output-33-1024x440.png)<figcaption>Output</figcaption>

## A Brief Synopsis

We hope that this tutorial gives you guidelines on how to ** find the method of a range object** in Excel VBA. Please leave a comment in case of any  **queries,**  and don’t forget to mention your valuable  **suggestions**  as well. Thank you so much for Visiting Our Site!! Continue learning on **[Geek Excel](https://geekexcel.com/)!! **Read more on [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

**Read Also:**

- **[How to use the XNPV function in Excel office 365?](https://geekexcel.com/how-to-use-the-xnpv-function-in-excel-office-365/)**
- **[Excel Formulas to Highlight the Cells that End with Given Value!!](https://geekexcel.com/excel-formulas-to-highlight-cells-end-with/)**
- **[Shortcut for “Select to End of Cell” in the Worksheet!!](https://geekexcel.com/quick-excel-shortcut-for-select-to-end-of-cell-in-a-worksheet/)**
- **[Excel Shortcuts: Delete to the End of Line in the Spreadsheet!!](https://geekexcel.com/shortcuts-for-delete-to-the-end-of-line-in-ms-excel-365/)**
- **[Excel Formulas for Counting Cells that End with certain character or text!!](https://geekexcel.com/excel-formulas-for-counting-cells-that-end-with-certain-character-or-text/)**