Whenever you use the _Change event to modify the Target cell, you
always want to turn off events. Otherwise, the Change code change
Target, which triggers Change, which changes Target, which triggers
Change, and on and on until VBA gives up when it runs out of stack
space.

Application.EnableEvents = False
Target.Value = whatever
Application.EnableEvents = True

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Tue, 24 Feb 2009 14:09:01 -0800, egun
wrote:

This is really ugly, but it will at least give you an idea of how to do what
you want. It seems to work as you described. Right-click on the worksheet
you want to put this code in, and select "View Code". Then paste this code
in. You'll have to modify it to look at whatever cell or cells your postal
code is in.

HTH,

Eric

Private Sub Worksheet_Change(ByVal Target As Range)
Dim chk1 As Boolean, chk2 As Boolean, chk3 As Boolean
Dim chk4 As Boolean, chk5 As Boolean, chk6 As Boolean
'
' Wherever you have the postal code...
If (Not Application.Intersect(Target, Range("A1")) Is Nothing) Then
If (Target.Text = "") Then Exit Sub ' Probably want some quality
checks...
If (Len(Target.Text) < 6) Then Exit Sub
' First part a digit?
chk1 = IsNumeric(Left(Target.Text, 1))
' Second part a letter (upper or lower case)
chk2 = (Asc(UCase(Mid(Target.Text, 2, 1))) = 65 And _
Asc(UCase(Mid(Target.Text, 2, 1) <= 90)))
' Third part a digit?
chk3 = IsNumeric(Mid(Target.Text, 3, 1))
' Fourth part a letter (upper or lower case)
chk4 = (Asc(UCase(Mid(Target.Text, 4, 1))) = 65 And _
Asc(UCase(Mid(Target.Text, 4, 1) <= 90)))
' Fifth part a digit?
chk5 = IsNumeric(Mid(Target.Text, 5, 1))
' Sixth part a letter (upper or lower case)
chk6 = (Asc(UCase(Mid(Target.Text, 6, 1))) = 65 And _
Asc(UCase(Mid(Target.Text, 6, 1) <= 90)))
If (chk1 And chk2 And chk3 And chk4 And chk5 And chk6) Then
Target = UCase(Left(Target.Text, 3)) & " " &
UCase(Right(Target.Text, 3))
End If
End If
End Sub