Table of Contents
Using VBA, you can loop through an entire column and search for a specific value. You can then use the Find command to locate the value and return the associated row or column number. Once you have the row or column number, you can use it to extract other data from that row or column. This allows you to quickly locate and return the value you are searching for.
You can use the following basic syntax in VBA to find a value in a column in Excel:
Sub FindValue()
Dim rng As Range
Dim cell As Range
Dim findString As String
'specify range to look in
Set rng = ActiveSheet.Columns("A:A")
'specify string to look for
findString = "Rockets"
'find cell with string
Set cell = rng.Find(What:=findString, LookIn:=xlFormulas, _
LookAt:=xlWhole, MatchCase:=False)
If cell Is Nothing Then
cell.Font.Color = vbBlack
Else
cell.Font.Color = vbRed
cell.Font.Bold = True
End If
End Sub
This particular macro will look for the string “Rockets” in all of column A of the currently active sheet and, if found, change the font color of the cell to red and make the font bold.
The following example shows how to use this syntax in practice.
Example: How to Find Value in Column Using VBA
Suppose we have the following dataset that contains information about various basketball players:
Suppose we would like to find the team name “Rockets” in column A and, when found, convert the font color of the cell to red and make the font bold.
We can create the following macro to do so:
Sub FindValues()
Dim rng As Range
Dim cell As Range
Dim findString As String
'specify range to look in
Set rng = ActiveSheet.Columns("A:A")
'specify string to look for
findString = "Rockets"
'find cell with string
Set cell = rng.Find(What:=findString, LookIn:=xlFormulas, _
LookAt:=xlWhole, MatchCase:=False)
If cell Is Nothing Then
cell.Font.Color = vbBlack
Else
cell.Font.Color = vbRed
cell.Font.Bold = True
End If
End Sub
When we run this macro, we receive the following output:
Notice that the font in the cell with the string “Rockets” is now red and bold.
All other cells simply kept their black font.
Note that the argument MatchCase:=False in the code tells VBA to perform a case-insensitive search.
Thus, if the team name in column A was “rockets” then the macro would still find this string and make the font red and bold.