VBA Find Value in Column

This article will demonstrate how to use VBA to find a value in a column.

We can use Range.Find to loop through a column of values in VBA to find all the cells in the range that match the criteria specified.

Looping through a column with Range.Find and Range.FindNext

In the example below, we are looping through the data in the column, and looking for the word “Overdue”.  When it finds the word, it will mark the cell by changing the color of the cell text to red.  It will then us the Range.FindNext method to move onto the next cell and continue to look for the word, continuing the loop until the end of the specified range of cells.

Sub FindLoop()
 Dim strFirstAddress As String
 Dim rngFindValue As Range
 Dim rngSearch As Range
 Dim rngFind As Range
 Set rngFind = ActiveSheet.Range("F1:F17")
 Set rngSearch = rngFind.Cells(rngFind.Cells.Count)
 Set rngFindValue = rngFind.Find("Overdue", rngSearch, xlValues)
 If Not rngFindValue Is Nothing Then
   strFirstAddress = rngFindValue.Address
   rngFindValue.Font.Color = vbRed
   Do
     Set rngFindValue = rngFind.FindNext(rngFindValue)
     rngFindValue.Font.Color = vbRed
  Loop Until rngFindValue.Address = strFirstAddress
 End If
End Sub

When the code runs, it saves the address of the first cell where the data is  found in the variable strFirstAddress and changes the color of the text to red. A loop is then created to find the next cell which contains the required data. When the value is found, the color of the text is changed to red and then the address of the cell where the value is found is compared to the string strFirstAddress.  If these are not the same, the loop continues, finding each instance of the word “Overdue”.  Once the loop reaches the end of the range of cells (ie F17), it will start back at the beginning of the range (F1) and continue to loop.  Once it reaches the cell address F3 for the second time, as it is the same as the stored variable strFirstAddress, the loop will stop.