Format Cells for UK Postcode

You are correct TM it also applies to

BIRMINGHAM, LONDON, GLASGOW, LIVERPOOL, MANCHESTER, SHEFFIELD


All of which have 1 letter & 1 digit codes

It gets a bit more interesting as it grows

Option Explicit

Function FormatPostCode(Target)
    Dim strChk As String

    With WorksheetFunction
        strChk = .Trim(Target)
        strChk = .Substitute(strChk, " ", "")
    End With

    Target.Interior.ColorIndex = xlNone
    If IsNumeric(Left(strChk, 1)) Then
        FormatPostCode = Target
        Target.Interior.ColorIndex = 3
    Else
        Select Case Len(strChk)
            Case Is < 2
                FormatPostCode = Target
                Target.Interior.ColorIndex = 3
            Case 2, 3
                If IsNumeric(Right(strChk, 1)) Then
                    FormatPostCode = UCase(WorksheetFunction.Trim(strChk))
                Else
                    FormatPostCode = Target
                    Target.Interior.ColorIndex = 3
                End If
            Case 4
                If IsNumeric(Right(strChk, 2)) Then
                    If Not IsNumeric(Right(strChk, 3)) Then
                        FormatPostCode = UCase(WorksheetFunction.Trim(strChk))
                    Else
                        FormatPostCode = Target
                        Target.Interior.ColorIndex = 3
                    End If
                End If
            Case Else
                strChk = Left(strChk, Len(strChk) - 3) & " " & Right(strChk, 3)
                FormatPostCode = UCase(WorksheetFunction.Trim(strChk))
        End Select
    End If
End Function

Sub CheckPostCodes()
    Dim LastRow As Long, RowNo As Long

    LastRow = Range("J" & Rows.Count).End(xlUp).Row
    For RowNo = 2 To LastRow
        If Range("K" & RowNo) = "United Kingdom" Then
            Range("J" & RowNo) = FormatPostCode(Range("J" & RowNo))
        End If
    Next
End Sub

This needs refining , and a little more error checking built into the function to make it bullet proof, but got to go for a while.

Give the attached a try.