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.
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 you 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 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
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.
'PURPOSE: Checks a specific column and validates that value follow a specified pattern (numbers or letter combinations)
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
'Were there any invalid patterns found?
If InvalidCount > 0 Then MsgBox "There were " & InvalidCount & _
" cells found not following the required pattern!"
Get The Example File!
As always, in order to download this article's example file you will need to be a subscriber of my free tips newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.
- 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 into 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!
How Do I Modify This To Fit My Specific Needs?
Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it!
I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).
I wish you the best of luck and I hope this tutorial gets you heading in the right direction!
Chris "Macro" Newman :)