Gos

This version verifies letters, numbers, space in correct location.

Change Column number to suit

For invalid Postcode entries if you remove the ' before 'rng.ClearContents it will clear the entry


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 Len(Rng.Value) <> 7 Then
            MsgBox "Invalid Post Code Entyry in " & Rng.Address
            Exit For
         End If
         For iChar = 1 To 7 Step 1
            Select Case Mid(Rng.Value, iChar, 1)
            Case "A" To "Z"
               Select Case iChar
               Case 2, 4, 5, 7
                  MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                  & " is invalid for Postcode"
                  'rng.ClearContents
                  Exit For
               End Select
            Case 0 To 9
               Select Case Mid(Rng.Value, iChar, 1)
               Case 1, 3, 4, 6
                  MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                  & " is invalid for Postcode"
                  'rng.ClearContents
                  Exit For
               MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                  & " is invalid for Postcode"
                  'rng.ClearContents
                  Exit For
               End Select
            Case " "
               Select Case Mid(Rng.Value, iChar, 1)
               Case 1 To 3, 5 To 7
                  MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                     & " is invalid for Postcode"
                  'rng.ClearContents
                  Exit For
               End Select
            Case Else
               MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                     & " is invalid for Postcode"
                  'rng.ClearContents
               Exit For
            End Select
         Next iChar
      End If
   Next Rng
   Application.EnableEvents = True
End Sub