VBA Find with What & After Arguments

An example of searching a Range with Find

Following is the sample sheet used for our examples:

In the first example, we will search for “Wheat” in our range of B2:B11.

Only What argument is given – which is the only required argument in the Find function.

Then we display the Address of the found cell:

VBA Code:

Sub find_ex()

Dim cell As Range
Set cell = Range("B2:B11").Find("Wheat")

MsgBox cell.Address

End Sub

Result:

Changing cell color example

In this example, we will change the cell color of the found cell.

We will search for “Rice” and change the font color to green by using the ColorIndex property:

Code:

Sub find_ex()

Dim cell As Range
Set cell = Range("B2:B11").Find("Rice")

cell.Font.ColorIndex = 17

End Sub

Output:

Changing whole row color example after find method

The color of the whole row (or other formatting options) can also be changed after the Find function is able to search data:

The following example changes the whole row color to red, with bold and italic style after searching “Out of Stock” in the D2 to D10 range:

Sub find_ex()

Dim cell As Range
Set cell = Range("D2:B10").Find("Out of Stock")

cell.EntireRow.Font.Color = RGB(255, 0, 0)
cell.EntireRow.Font.Bold = True
cell.EntireRow.Font.Italic = True


End Sub

Result:

Using the After argument in the Find function example

As the Find function returns a range object that represents the first cell where information is found, what if the sheet contains multiple occurrences of the search terms?

Like in our sheet, “Out of Stock” exists twice.

The example below shows the usage of the “After” argument where you may specify a cell. We will apply a style to the second occurrence:

Code:

Sub find_ex()
Dim cell As Range

'Speicfying What to search and After which cell
Set cell = Range("D2:B10").Find("Out of Stock", After:=Range("D7"))

cell.EntireRow.Font.Color = RGB(0, 0, 155)
cell.EntireRow.Font.Bold = True
cell.EntireRow.Font.Italic = True

End Sub

Result: