VBA Pattern Validation (Characters And Numbers)
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.
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 cellNext, 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 cellAdding 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 SubDownload 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!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
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.