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.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Write AVERAGEIF and AVERAGEIFS Functions
VBA: How to Write SUMIF and SUMIFS Functions
VBA: How to Write COUNTIF and COUNTIFS Functions
