To be honest Aiden, im not too sure myself!, I have been battling with
this for hours now and as I cannot really get my head around the
formula itself I have resorted to the vba code for the answer,

It would have been so much better to have it respond to data validation
but I think because "If the check digit value is 10, add 1 to the base
number and repeat the process." involves looping round again it keeps
giving me wrong answers.

I think I have it working fine now in the code (which I will post below
for the benefit of others) and I am going to give the validation way a
rest for now!

Many thanks for your help on this.

Duncan

(below code allows for TAB or ENTER away from cell, im sure this is not
the most efficient but it works! (I think))

Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveCell.Column = 1 Then

On Error Resume Next

num = ActiveCell.Offset(-1, 0).Value
Set num2 = ActiveCell.Offset(-1, 0)
remainder = (Mid(num, 1, 1) * 13 + Mid(num, 2, 1) * 11 +
Mid(num, 3, 1) * 7 + Mid(num, 4, 1) * 5 + Mid(num, 5, 1) * 3 + Mid(num,
6, 1) * 2) Mod 11
check = 11 - remainder

While check = 10
check = check + 1
check = 11 - check
Wend

If check = 11 Then
check = 0
End If

num3 = (Mid(num, 1, 6))

FINnum = num3 & check

If Trim(FINnum) < Trim(num2) Then
num2.Select
ActiveCell.Interior.ColorIndex = 3
MsgBox "This is not a Valid Registration Number!"
End If

If Trim(FINnum) = Trim(num2) Then
If num2.Interior.ColorIndex = 3 Then
num2.Select
ActiveCell.Interior.ColorIndex = 0
End If
End If

End If

If ActiveCell.Column = 2 Then

On Error Resume Next

NUM0 = ActiveCell.Offset(0, -1).Value
Set NUM22 = ActiveCell.Offset(0, -1)
REMAINDERR = (Mid(NUM0, 1, 1) * 13 + Mid(NUM0, 2, 1) * 11 +
Mid(NUM0, 3, 1) * 7 + Mid(NUM0, 4, 1) * 5 + Mid(NUM0, 5, 1) * 3 +
Mid(NUM0, 6, 1) * 2) Mod 11
CHECKK = 11 - REMAINDERR

While CHECKK = 10
CHECKK = CHECKK + 1
CHECKK = 11 - CHECKK
Wend

If CHECKK = 11 Then
CHECKK = 0
End If

NUM33 = (Mid(NUM0, 1, 6))

F1nN3m = NUM33 & CHECKK

If Trim(F1nN3m) < Trim(NUM22) Then
NUM22.Select
ActiveCell.Interior.ColorIndex = 3
MsgBox "This is not a Valid Registration Number!"
End If

If Trim(F1nN3m) = Trim(NUM22) Then
If NUM22.Interior.ColorIndex = 3 Then
NUM22.Select
ActiveCell.Interior.ColorIndex = 0
End If
End If

End If




End Sub