Repeated Search and Finding All Values with Excel VBA

The FindNext and FindPrevious methods of the Range object allow repeating the Find method to continue a specified search. The first method searches for the next cell, while the second searches for the previous cell that meets the search criteria.

FindNext(After)
FindPrevious(After)

Here, After is an optional parameter indicating the cell after which the search should continue.

As an example, the following code searches for the substring « BHV » case-insensitively in the range A1:A10. All found cells are filled with yellow.

Finding All Occurrences of a Substring in a Range

Sub Find2()
    Dim firstAddress As String
    Dim rng As Range   
    Set rng = Range("A1:A10").Find(What:="BHV", LookIn:=xlValues, _
        LookAt:=xlPart, MatchCase:=False)   
    If Not (rng Is Nothing) Then
        firstAddress = rng.Address
        Do
            rng.Interior.Color = RGB(255, 255, 0)
            Set rng = Range("A1:A10").FindNext(rng)
        Loop While Not (rng Is Nothing) And rng.Address <> firstAddress
    End If
End Sub