Pattern Validation: Ensure Cell Value Follows a Specified Character & Number Combination

Pattern Validation - Header.jpg

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 you target range with VBA code.

1. ActiveSheet.UsedRange.Select
2. ActiveSheet.UsedRange.Columns(3).Select
3. ActiveSheet.UsedRange.Columns(3).Offset(1).Select
4. ActiveSheet.UsedRange.Columns(3).Offset(1).Resize(ActiveSheet.UsedRange.Rows.Count - 1, 1).Select

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)

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

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.

                   Already Subscribed? Click HERE to log-in to the "Example Files" section

                   Already Subscribed? Click HERE to log-in to the "Example Files" section


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 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 :)