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 inSet rng = ActiveSheet.Columns("A:A")

'specify string to look for
findString = "Rockets"

'find cell with stringSet 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 IfEnd 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 inSet rng = ActiveSheet.Columns("A:A")

'specify string to look for
findString = "Rockets"

'find cell with stringSet 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 IfEnd 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.