Validation of postcodes in several countries

Question

I am wanting a code to check all postcodes in say column A and turn the cell green if it passes the REGEX pattern and red if it doesn't. I have been using the below code:

sub postcode()


    Dim strPattern As String
    Dim regEx As Object
    Dim ncella As Long, i As Long
    Dim rng As Range

    Set regEx = CreateObject("VBScript.RegExp")
    regEx.Global = True
    regEx.IgnoreCase = True

    ncella = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlDown).Row


    For i = 1 To 2
        If i = 1 Then
            strPattern = "(\d{4})"
            Set rng = Range("A2:A" & ncella)

        End If
        regEx.Pattern = strPattern

        For Each cell In rng.Rows                   ' Define your own range here
            If strPattern <> "" And cell <> "" Then ' If the cell is not empty and there is       pattern
            If regEx.test(cell.Value) Then   ' Check if there is a match
                cell.Interior.ColorIndex = 4 ' If yes, change the background color
            Else
                cell.Interior.ColorIndex = 3
            End If
        End If
    Next
Next i

End Sub

Now I also want to use this check for validating the postcode patterns for several countries so (once my code above or any other code is provided) then I would need the patterns to be validated once the ISO country code is selected by the user, essentially applying the pattern to the specific country.

Hope the above makes sense

Now in the above is a simple check to see if there are 4 digits in the postcode then turn Green. If I paste in a number with 1,2,3 or 4 digits the cell correctly turns red, however if I have 6 or more digits it turns green for some reason. Having 5 digits also turns the cell green which is correct.

score 0 · Answer 1 · answered Apr 03 '23 at 13:38

0

Why are you making this so difficult?
When a value consists of nothing but digits, it automatically represents a number you can check. In case if consists other characters, the number value equals zero. So, if you want to check if a four-digit number contains nothing but digits (not starting with zero), you can simply check if the value is between 1000 and 9999, as in following screenshot:

The colours are based on conditional formatting. That conditional formatting is based on the formula in column "B":

=AND(A2>=1000,A2<=9999)

answered Apr 03 '23 at 13:38

Dominique

16,450
15
56
112

thanks for that, makes sense but how would I for example convert the below regex for GB postcodes:([Gg][Ii][Rr] 0[Aa]{2})|((([A-Za-z][0-9]{1,2})|(([A-Za-z][A-Ha-hJ-Yj-y][0-9]{1,2})|(([A-Za-z][0-9][A-Za-z])|([A-Za-z][A-Ha-hJ-Yj-y][0-9]?[A-Za-z])))) [0-9][A-Za-z]{2}) to a formula? – JohnA Apr 03 '23 at 14:41
My answer is based on your proposal `"(\d{4})"`, but indeed, the very weird postal codes from UK (including SW1W 0NY", "PO16 7GZ", "GU16 7HF", ...) don't fit in my line of reasoning, sorry. – Dominique Apr 03 '23 at 14:46
1

related & discussing further caveats [RegEx for matching UK postcodes](https://stackoverflow.com/questions/164979/regex-for-matching-uk-postcodes/164994#164994) – T.M. Apr 03 '23 at 17:04
Thanks for the above it was useful for the GB codes. I was also wondering if it's possible to combine the REGEX with a vlookup in VBA so that I can apply validation rules per country from one data source based on the ISO country code? – JohnA Apr 06 '23 at 07:02

Validation of postcodes in several countries

1 Answers1