Gajendra
Fantastic bit of code - I would never have thought of using Like
I have modified my macro to incorporate your code.
The new macro retains all the requirements as per Gos-C 1st posting and is much simpler than what I had coded
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim iChar As Integer
For Each Rng In Target
If Rng.Column = 1 Then
Application.EnableEvents = False
Select Case Len(Rng.Value)
Case 6
Rng.Value = UCase(Left(Rng.Value, 3) & " " & Right(Rng.Value, 3))
Case 7
Rng.Value = UCase(Rng.Value)
End Select
If Rng.Value Like "[A-Z][0-9][A-Z][ ][0-9][A-Z][0-9]" Then
'do nothing
Else
MsgBox Rng.Address & " - invalid Postcode"
End If
End If
Next Rng
Application.EnableEvents = True
End Sub