Validation of postcodes in several countries

0 votes

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.

Apr 4, 2023 in Others by narikkadan
• 63,720 points
305 views

1 answer to this question.

0 votes

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:

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 4, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Reducing sequences in an array of strings

I've written a C# app to solves ...READ MORE

answered Nov 2, 2018 in Others by DataKing99
• 8,240 points
798 views
0 votes
2 answers

How to get the URL of the current tab in Google Chrome?

Its so simple.... If you want to ...READ MORE

answered Aug 12, 2020 in Others by Steve
• 200 points
3,324 views
0 votes
1 answer

Where is the documentation to refer for coinbase api integration of Etherium coin currency in php?

Hey there! Please refer to the following ...READ MORE

answered Jan 25, 2019 in Others by Omkar
• 69,230 points
615 views
0 votes
0 answers

In 2019, AtoX leads the new trend of decentralized exchanges

Under the influence of the blockchain continuing ...READ MORE

Jan 29, 2019 in Others by anonymous
362 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,006 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,371 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
679 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
889 views
0 votes
1 answer
0 votes
1 answer

Excel VBA: Obtain the Column and Row of current element in "For Each" loop

Try this: MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=F ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,010 points
954 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP