Here is some worksheet event code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fOK As Boolean

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
fOK = False
If Len(.Value = 4) Then

If UCase(Left(.Value, 1)) = "A" And _
UCase(Left(.Value, 1)) <= "Z" Then

If UCase(Mid(.Value, 2, 1)) = "A" And _
UCase(Mid(.Value, 2, 1)) <= "Z" Then

If Mid(.Value, 3, 1) = 0 And _
Mid(.Value, 3, 1) <= 9 Then

If Mid(.Value, 4, 1) = 0 And _
Mid(.Value, 4, 1) <= 9 Then

fOK = True

End If
End If
End If
End If
End If
End With
If Not fOK Then
MsgBox "Invalid value"
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Kim-Anh Tran " wrote in
message ...
How do I set Customized validation to allow first alpha two digits and
follow by two numeric digits. (Like CN15)
Thank you for your help!
Kim-Anh


---
Message posted from http://www.ExcelForum.com/