×

VBA Pattern Validation (Characters And Numbers)

By Chris Newman •  Updated: 04/07/14 •  5 min read
Pattern Validation

Today we are going to discuss how you can use VBA to ensure that cell values match a specific pattern.  There might be instances where you want your data to start with three characters and end with three numbers (such as the typical format for a license plate number).  Verifying text patterns cannot be performed through a spreadsheet Data Validation rule, so you will have to write a VBA macro to simulate this form of validation.

The Scenario

We want to create a macro that highlights cells in yellow whose values do not match a format of three characters followed by three numbers (an example could be: ABC123).  This macro will only need to look in Column C since the license plate data is currently stored in that particular column.

Determining Our Target Range

First, you are going to want to narrow down the cells in your worksheet that your code analyzes.  To do this, you can first use UsedRange to select only the cells in your worksheet that contain values.

Next, since you know your data starts in Cell A1, you can reference a column number in your UsedRange (in this case since we want column C, we will use the number 3). 

Now that you have the column narrowed down, you will want to exclude the header row since it will not follow the same pattern as the rest of the data.  To do this you can use a combination of the Offset and Resize functions so that the target range excludes the first row. Below is a visual representation of how you can narrow down your target range with VBA code.

Pattern Validation

Loop Through Cells in Target Range And Test Pattern

Now that you have your target range defined, you will want your macro code to loop through each cell in the range and test the cell values to see if they match your criteria.  To do this you can create a Range Object and use a For Each loop to cycle through all the cells in your target range.

Dim cell As Range

For Each cell In Rng.Cells

Next cell

Next, you can use an IF statement to test for the pattern criteria. To test for patterns you will need to use the Like operator.  The Like operator can test for a bunch of criteria but you will only need to use two of its powers. 

First, you can use a phrase such as [A-N] to test and see if a character falls between a specific range in the alphabet.  Since you want to include all the letters in the alphabet, you will want to use [A-Z].  The other expression you will want to use is the pound sign (#), which tests for a numerical value. Below is how you should construct your loop:

Dim cell As Range

For Each cell In Rng.Cells

  If Not cell.Value Like "[A-Z][A-Z][A-Z]###" Then
  
  End If

Next cell

Adding The Finishing Touches

To finish this macro off you can add a few features to report back to the user when any invalid cell values are found.  You can add:

  • A variable to allow the user to determine which column in the data set to validate
  • The use of the UCase( ) function to take away any case sensitivity
  • Fill any cell failing the pattern test with the color yellow
  • A notification if any of the cells fail the pattern validation

Below is the final solution VBA code which validates if the cells in column 3 of the data set follow the pattern of three letters followed by three numbers.

Sub ValidatePattern()

'PURPOSE: Checks a specific column and validates that value follow a specified pattern (numbers or letter combinations)
'SOURCE: www.TheSpreadsheetGuru.com

Dim cell As Range, rng As Range
Dim InvalidCount As Long, x As Long

x = 3 'Column to Validate

Set rng = ActiveSheet.UsedRange.Columns(x).Offset(1).Resize(ActiveSheet.UsedRange.Rows.Count - 1, 1)

For Each cell In rng.Cells

  If Not UCase(cell.Value) Like "[A-Z][A-Z][A-Z]###" Then
    'Highlight Invalid Cell Yellow
      cell.Interior.Color = RGB(255, 255, 0)
    
    'Add Instance to Invalid Counter
      InvalidCount = InvalidCount + 1
  End If

Next cell

'Were there any invalid patterns found?
  If InvalidCount > 0 Then MsgBox "There were " & InvalidCount & _
    " cells found not following the required pattern!"

End Sub

Download Example Excel File

If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.

Further Resources

  • Read through my free Intro to VBA lessons to learn how you can easily implement this macro code and become familiar with how to start coding VBA on your own!
  • Learn more about the Like Operator and its amazing powers!

How Will You Use Pattern Validation?

I'm curious to hear from you about how you can apply pattern validation to your projects. Will you create a personal macro that you run on the fly?  Or, will you modify the code and incorporate it into a larger macro code?  Leave me a comment below and share your thoughts!


Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.