Formatting Issues

[Claire]

Hi,

Hope someone can help.....

Excel is acting very strange.
I have been formatting excel in the same way I'm about to
explain for quite some time with no problems ... until
today... twitch, twitch...

I need to format Columns B & C to display 5 leading 0's.
Col A displays 7 leading zero's and Col D with 15.

Col A and D are fine. However, if I go back into
formatting and select col's B or C, they have defaulted
automatically to "Special Formatting" on the "Zip Code"
format instead of remaining on "Custom".
Any suggestions would be greatly appreciated

Regards,

Claire

 

[Dave Peterson]

I never noticed this before, but it looks like excel doesn't care about why you
formatted your range to have up to 5 leading 0's.

00000 is the same format as the 5 digit zipcode.

On the other hand, VBA doesn't call the format Zip. It uses 00000.

Format a cell (A1) with for a 5 digit zip and another cell (A2) as custom 00000.

then hit alt-F11 (to get to the VBE) and then hit ctrl-G (to see the immediate
window):

Type this in and hit enter:

?range("a1").numberformat

Then
?range("a2").numberformat

You'll see "00000" returned for both.

I got:

?range("a1").NumberFormat
00000
?range("a2").NumberFormat
00000

So I don't think it's anything you have to worry about.