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.

Answer 1

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)

Answer 2

I made a validation formula for Dutch postal code (it works, perhaps is a shorter version possible):

=EN(ISGETAL(WAARDE(LINKS(A1;4))); LENGTE(A1)=7; EN(ISTEKST(A1); CODE(RECHTS(A1;1)) >= 65; CODE(RECHTS(A1;1)) <= 90; CODE(RECHTS(A1;2)) >= 65; CODE(RECHTS(A1;2)) <= 90); CODE(RECHTS(A1;3)) = 32)