VBA: How to Use Like Operator

This tutorial explains how to use Like Operator in VBA.

Like Operator is used to compare a string against a pattern.

Syntax of Like Operator

Variable Like pattern

Variable is the string we want to compare. pattern is the search pattern we are comparing against which can include wildcards.

You can download the following dataset to practice.

Let us consider a few examples to understand the practical applications of Like Operator.

1. Highlight the Names Starting with letter 'A'

The following code can be used to highlight all names that begin with 'A' in column A of the dataset.

Sub HighlightNamewithA()
    Dim ws As Worksheet
    Dim searchPattern As String
    Dim lastRow As Long
    Dim Cell As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        searchPattern = "A*"

        For Each Cell In ws.Range("A1:A" & lastRow)
    
        If Cell.Value Like searchPattern Then
            Cell.Interior.Color = vbRed
        End If
        Next Cell
End Sub

Press Run or F5 to run the above code.

VBA : Highlihght Names Using Like operator

2. Emails With Exactly Three Characters Before "@"

The following code can be used to highlight emails with exactly three characters before the '@' in column B of the dataset.

Sub email()
    Dim ws As Worksheet
    Dim searchPattern As String
    Dim lastRow As Long
    Dim Cell As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        searchPattern = "???@*"

        For Each Cell In ws.Range("B1:B" & lastRow)
    
        If Cell.Value Like searchPattern Then
            Cell.Interior.Color = vbRed
        End If
        Next Cell
End Sub

Press Run or F5 to run the above code.

3. Phone Numbers Containing '444'

In this case, let's highlight all the phone numbers in column C that contain '444' in them.

Sub phonenumber()
    Dim ws As Worksheet
    Dim searchPattern As String
    Dim lastRow As Long
    Dim Cell As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        searchPattern = "*444*"

        For Each Cell In ws.Range("C1:C" & lastRow)
    
        If Cell.Value Like searchPattern Then
            Cell.Interior.Color = vbRed
        End If
        Next Cell
End Sub

Press Run or F5 to run the above code.

VBA: Wildcards with Like Operator in VBA

4. Product IDs ending with '3'

We can use the following code to highlight all the product IDs (in column D) that end with '3' :

Sub productid()
    Dim ws As Worksheet
    Dim searchPattern As String
    Dim lastRow As Long
    Dim Cell As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
         searchPattern = "*3"

        For Each Cell In ws.Range("D1:D" & lastRow)
    
        If Cell.Value Like searchPattern Then
            Cell.Interior.Color = vbRed
        End If
        Next Cell
End Sub

Press Run or F5 to run the above code.

About Author:
Yash Gaur

Yash is pursuing an MBA in Finance with a keen interest in analytics. He enjoys working with data and leveraging his research and analytical skills to generate valuable insights.

Let's Get Connected Email LinkedIn