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.
