Validation of postcodes in several countries

Question

I need a programme that will examine each postcode in, let's say, column A and change the cell green if it matches the REGEX pattern and red otherwise. I've been using the code below:

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 that I've mentioned it, I also want to use this check to validate the postcode patterns for a number of different countries, so (once my code above or any other code is provided) I'll need the patterns to be validated once the user selects the ISO country code, effectively applying the pattern to the chosen country.

I hope that made sense.

Now that the postcode has four digits, it is only a simple matter to check and go green if it does. The cell turns red when I paste a number with 1, 2, 3, or 4 digits; however, for some reason, if I have 6 or more digits, the cell becomes green. The cell becomes green when there are 5 digits, which is accurate.

Answer 1

When a value is made up entirely of digits, it immediately reflects a verifiable number. If it contains further characters, the number value is equivalent to zero. So, you can simply check if the value is between 1000 and 9999 to see if a four-digit number is made up entirely of digits (and does not begin with zero) as shown in the screenshot below:

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

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