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.
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!