Why not let the code handle that for you too? You can enter your Canadian
postal codes with or without the space and you can enter any 9-digit US Zip
Codes (assuming you use those) with or without the dash between the first 5
digits and the last 4 digits...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Temp As String
If Intersect(Range(Target(1).Address), _
Range("F:F")) Is Nothing Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
With Target
If Not .HasFormula Then
Temp = Replace(UCase(Target.Value), " ", "")
If Len(Temp) = 6 Then
.Value = Format(Temp, "@@@ @@@")
ElseIf Len(Temp) = 9 Then
.Value = Format(Temp, "@@@@@-@@@@")
End If
End If
End With
With Target.Offset(0, -1)
.Formula = UCase(.Formula)
End With
ErrHandler:
Application.EnableEvents = True
End Sub

--
Rick (MVP - Excel)


"Michael Koerner" wrote in message
...
Gord;

Absolutely correct, I did just want the Prov in upper case. Works like a
charm until I enter a US zip code. But, that is not a problem, I have
fewer
of those and it is easy to delete a character. Thank you very much

--

Regards
Michael Koerner


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
A worksheet cannot have multiple events of the same type.

Different types...........yes.........same type........no.

Sounds like you have more than one worksheet_change event in that sheet
module.

Either combine or change one of them to a different type.

BTW...............postal codes are in column 6...........is column 5
simply
province names?

I can't imagine you want the same format on both columns in that case.

Assumes you enter a province name in column 5 then a postal code in column
6

After the column 6 entry the code will run on both columns.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range(Target(1).Address), _
Range("F:F")) Is Nothing Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
With Target
.Formula = UCase(Target.Formula)
.Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3))
End With
With Target.Offset(0, -1)
.Formula = UCase(.Formula)
End With
ErrHandler:
Application.EnableEvents = True
End Sub


Gord

On Sun, 17 May 2009 06:51:15 -0400, "Michael Koerner"
wrote:

After a good nights sleep, your suggestion really made a lot of sense.
thank
you very much.

But, when I tried to insert the macro you provided, I received a compile
error indicating I had an ambiguous statement.

I also have a couple of questions. The column with the postal code is
column
6. Your macro I believe indicated <7 I would also like to have column 5
if
possible in Upper Case as this is the prov/state column.