Method 1 – Finding Multiple Values from User Input
The following code will prompt users to insert a value that they want to find and highlight all the occurrences of that value within the dataset.
Sub Find_from_UserInput()
Dim Rng As Range
Set Rng = Range("C5:C14")
user = InputBox("Please insert an Author's name")
For i = 1 To Rng.Rows.Count
If LCase(Rng.Cells(i, 1)) = LCase(user) Then
Range(Rng.Cells(i, 1).Offset(0, -1), Rng.Cells(i, 1).Offset(0, 2)).Interior.Color = vbGreen
End If
Next i
End SubCode Breakdown:
Dim Rng As Range
Set Rng = Range("C5:C14")
user = InputBox("Please insert an Author's name")- A Range type variable Rng is declared and assigned the C5:C14 range as its value.
- The user variable will store the author’s name that the user will input in the InputBox.
For i = 1 To Rng.Rows.Count
If LCase(Rng.Cells(i, 1)) = LCase(user) Then
Range(Rng.Cells(i, 1).Offset(0, -1), Rng.Cells(i, 1).Offset(0, 2)).Interior.Color = vbGreen
End If
Next i- For Loop will run through the rows of the Rng range and match the values of each cell with the value of the user variable.
- If the values match then the Range(Rng.Cells(i, 1).Offset(0, -1), Rng.Cells(i, 1).Offset(0, 2)).Interior.Color = vbGreen line will highlight the entire row of the matched value with green.
Follow the steps below to execute the method shown in the video:
- Run the VBA Code.
- Write the author’s name whose information you want to find.
- The code will highlight the information in green.
Read More: Excel VBA to Find Matching Value in Column
Method 2 – Using Array to Find Multiple Values
In this instance, we will build an array containing the values to find. Then, the VBA code will highlight the values related to those values within the array.
Sub Find_from_Array()
Dim Rng As Range
Dim Author() As Variant
Set Rng = Range("C5:C14")
Author = Array("George R.R.Martin", "Siddhartha Mukherjee")
For i = 0 To UBound(Author)
For j = 1 To Rng.Rows.Count
If Rng.Cells(j, 1) = Author(i) Then
Range(Rng.Cells(j, 1).Offset(0, -1), Rng.Cells(j, 1).Offset(0, 2)).Interior.Color = vbGreen
End If
Next j
Next i
End SubCode Breakdown:
Dim Rng As Range
Dim Author() As Variant
Set Rng = Range("C5:C14")
Author = Array("George R.R.Martin", "Siddhartha Mukherjee")- Here, the code declares two variables Rng and Author.
- Then, it assigns the C5:C14 range as the value of the Rng variable and builds an array using the Array function, with the elements of the array being the names of two authors.
For i = 0 To UBound(Author)
For j = 1 To Rng.Rows.Count
If Rng.Cells(j, 1) = Author(i) Then
Range(Rng.Cells(j, 1).Offset(0, -1), Rng.Cells(j, 1).Offset(0, 2)).Interior.Color = vbGreen
End If
Next j
Next i- This portion has a For Loop within a For Loop.
- The first For Loop iterates through the values of the Author array, and the next one loops through the rows of the Rng range.
- Then it checks if the value in each row matches the current element of the Author array, and if so, it highlights a range of cells in that row with a green interior color.
Follow the steps below to execute the method:
- Run the code by clicking on the green triangle in the VBA module.
- The code will highlight the information of the authors present in the Author array (marked with a red rectangle).
Read More: Excel VBA to Find Value in Column
Method 3 – Finding Multiple Values from Selection
In this example, we will select some values from the Excel sheet, and the VBA code will highlight the information related to those values from the dataset.
Sub Find_From_Selection()
'declaring variables
Dim Rng As Range
Dim Author() As Variant
Dim user As Range
Set Rng = Range("C5:C14")
'setting the value of the user variable to selected range in the worksheet
Set user = Selection
'counting row numbers of the selection
nRow = user.Rows.Count
'redimesionalizing the Author array
ReDim Author(1 To nRow)
'taking all the values of the user range into Author array
For i = 1 To nRow
Author(i) = user.Cells(i, 1)
Next i
'running for loop to find match for array values and highligh them
For i = 1 To UBound(Author)
For j = 1 To Rng.Rows.Count
If Rng.Cells(j, 1) = Author(i) Then
Range(Rng.Cells(j, 1).Offset(0, -1), Rng.Cells(j, 1).Offset(0, 2)).Interior.Color = vbGreen
End If
Next j
Next i
End SubCode Breakdown:
- Here, we declare three variables, namely: Rng, Author, and user. We then assign their values. The value of the user variable is a range that is selected by the users before they run the code.
- nRow = user.Rows.Count – this line counts the row number present in the user range. With ReDim Author(1 To nRow), the code resizes the Author array which has an index from 1 to the number of rows the user range has.
For i = 1 To nRow
Author(i) = user.Cells(i, 1)
Next i- For Loop takes all the values of the selection range inside the Author array.
For i = 1 To UBound(Author)
For j = 1 To Rng.Rows.Count
If Rng.Cells(j, 1) = Author(i) Then
Range(Rng.Cells(j, 1).Offset(0, -1), Rng.Cells(j, 1).Offset(0, 2)).Interior.Color = vbGreen
End If
Next j
Next i- This code loops over the elements of the Author array, and for each element, it loops over the rows in the Rng range.
- For each row, it determines whether the value in the first column matches the current element of the Author array, and if it does, it highlights a range of cells in that row with a green interior color.
Follow the steps below to execute the task as shown in the video:
- From the Find Author(s) column select the authors’ names whose information you want to find.
- Run the VBA code.
- The relevant data will be highlighted in green.
Method 4 – Using Range.Find Method to Find Multiple Values
The Range.Find method can find values that match exactly or partially with the input value. All we need to do is change the MatchCase argument of the method.
4.1 Finding Exact Match
In this example, we will insert an author’s name into an InputBox and the code will highlight the information of the author whose name exactly matches the input.
Sub RangeDotFindMethod1()
'declaring variables
Dim Rng As Range
Dim AuthorCell As Range
Dim Author As String
Dim FirstCell As String
'prompting users to input an author's name
Author = InputBox("Please Type an Author Name")
Set Rng = Range("C5:C14")
'using Find method to find the cell number where the input author first appeared
Set AuthorCell = Rng.Find(What:=Author, MatchCase:=True)
'error handling if the author's name is not on the list
If AuthorCell Is Nothing Then
MsgBox "No author of such name is found"
Else
'running through the Rng range to find and highlight the author's information
FirstCell = AuthorCell.Address
Do
Range(AuthorCell.Offset(0, -1), AuthorCell.Offset(0, 2)).Interior.Color = vbGreen
Set AuthorCell = Rng.FindNext(AuthorCell)
Loop While AuthorCell.Address <> FirstCell
End If
End SubCode Breakdown:
Here, the code declares 4 variables, namely: Rng, AuthorCell, Author, and FirstCell. Then, it asks the user to write an author’s name and store that inside the Author variable. It also sets the C4:C14 range as the value of the Rng variable.
Set AuthorCell = Rng.Find(What:=Author, MatchCase:=True)- This line searches the Rng range of cells for a cell that matches the Author value.
- The Find method is used to perform the search, which returns the first cell that matches the search criteria.
- The What argument specifies the value to search for, which in this case is the Author value.
- The MatchCase argument is set to True, which means that the search is case-sensitive.
- The result of the search is stored in the AuthorCell object variable.
If AuthorCell Is Nothing Then
MsgBox "No author of such name is found"
Else
'running through the Rng range to find and highlight the author's information
FirstCell = AuthorCell.Address
Do
Range(AuthorCell.Offset(0, -1), AuthorCell.Offset(0, 2)).Interior.Color = vbGreen
Set AuthorCell = Rng.FindNext(AuthorCell)
Loop While AuthorCell.Address <> FirstCell
End IfIf AuthorCell Is Nothing Then
MsgBox "No author of such name is found"If AuthorCell is empty or the code does not find any matching value with the Author value, then this line displays a message box to the user indicating that no matching author was found.
- FirstCell = AuthorCell.Address: This line stores the address of the first match found by the Find method in the FirstCell variable.
- Do: This starts a Do Loop that will run at least once and continue to run until the AuthorCell.Address value matches the FirstCell value (i.e. until all matching cells have been processed).
- Range(AuthorCell.Offset(0, -1), AuthorCell.Offset(0, 2)).Interior.Color = vbGreen: This line selects a range of cells based on the current value of AuthorCell, using the same syntax as in the previous code examples. The interior color of this range is set to green using the vbGreen constant.
- Set AuthorCell = Rng.FindNext(AuthorCell): This line uses the FindNext method to find the next cell that matches the search criteria (i.e., the same Author value). The result of this search is stored in the AuthorCell object variable.
- Loop While AuthorCell.Address <> FirstCell: This line ends the Do loop, but only if the address of the AuthorCell value is not equal to the address of the FirstCell value (i.e., there are more matching cells to process). If the addresses match, then all matching cells have been processed, and the loop ends.
Follow the steps shown in the video:
- Execute the code from the VBA window.
- The code will ask for the name of any author.
- Enter the name.
- All the values related to that author will be marked with green.
4.2 Finding Partial Match
Here, we will insert a proportion of authors’ names or any letter in an InputBox and the code will find all the values that match partially with that input.
Sub RangeDotFindMethod2()
'declaring variables
Dim Rng As Range
Dim AuthorCell As Range
Dim Author As String
Dim FirstCell As String
'prompting users to input an author's name
Author = InputBox("Please Type an Author Name")
Set Rng = Range("C5:C14")
'using Find method to find the cell number in which the input first partially matches
Set AuthorCell = Rng.Find(What:=Author, MatchCase:=False)
'error handling if the author's name is not on the list
If AuthorCell Is Nothing Then
MsgBox "No author of such name is found"
Else
'running through the Rng range to find and highlight the author's information
FirstCell = AuthorCell.Address
Do
Range(AuthorCell.Offset(0, -1), AuthorCell.Offset(0, 2)).Interior.Color = vbGreen
Set AuthorCell = Rng.FindNext(AuthorCell)
Loop While AuthorCell.Address <> FirstCell
End If
End Sub
This code is the same as the code in the method of section 4.1. The only difference is that here in the Set AuthorCell = Rng.Find(What:=Author, MatchCase:=False) line the MatchCase argument is False which means the Find command will run a case-insensitive search. That is why the code will highlight all the values that match partially with the input.
Execute the method by applying the following steps as shown in the video:
- Execute the VBA code from the VBA module.
- Enter any letter or any portion of any author’s name in the InputBox.
- The code will highlight all the partial matches.
Download Practice Workbook
Related Articles
- How to Find Blank Cells Using VBA in Excel
- How to Find Exact Match Using VBA in Excel
- FindNext Using VBA in Excel
- How to Find Last Row Using Excel VBA
- Find Last Row with Data in a Range Using Excel VBA Macros
- Excel VBA: Find the Next Empty Cell in Range
