UK National Insurance Number Validation

[mykal66]

Hi guys i need help validating a UK National Insurance Number in a cell range G2:G200. Format must be LLNNNNNNL (letter case isn't too important but would prefer upper case)

I need any incorrect entry to be highlighted in and different font colour to draw users attention to the incorrect format or a message to advise user of same issue.

I would prefer to use VBA (but have tried conditional formatting too which i also couldn't get to work )and found some code that i cannot get to work as below - can anyone help with the code or have an alternative option

[VBA]Public Function IsNatInsurNum(DataInput As Variant) As Boolean
Dim mydata As String, Temp As Long
IsNatInsurNum = False
mydata = CStr(DataInput) 'convert to string
If Len(mydata) = 9 Then 'nine characters
If (Asc(Left(mydata, 1)) >= 65 And Asc(Left(mydata, 1)) <= 90)
Then 'first char is A to Z
If (Asc(Mid(mydata, 2, 1)) >= 65 And Asc(Mid(mydata, 2, 1)) <=
90) Then 'second char is A to Z
On Error Resume Next
Err.Clear
Temp = CDbl(Mid(mydata, 3, 6)) 'check 6 digits in middle
If Temp <> 0 Then
If (Asc(Right(mydata, 1)) >= 65 And
Asc(Right(mydata, 1)) <= 90) Then 'last char is A to Z
IsNatInsurNum = True
End If[/VBA]

[Bob Phillips]

[VBA]Public Sub Test()
Dim RegEx As Object
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "^[A-CEGHJ-PR-TW-Z]{1}[A-CEGHJ-NPR-TW-Z]{1}[0-9]{6}[A-DFM]{0,1}$"
RegEx.Global = True

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "G").End(xlUp).Row
For i = 2 To Lastrow

With .Cells(i, "G")

If Not RegEx.Test(.Value2) Then

With .Font

.ColorIndex = 3
.Bold = True
End With
End If
End With
Next i
End With

Set RegEx = Nothing

Application.ScreenUpdating = True
End Sub
[/VBA]

[mykal66]

Hi

Thanks for your help but when i run it it turns the whole column red rather than just cells with the incorrect National Insurance Number format

[GTO]

Hi there,

Could you provide some sample data? Post a workbook in .xls format, with a decent ampunt of examples in column G and maybe include which ones should be marked in an adjacent column.

Mark