formatting a cell for both ZIP and ZIP+4
 

I am using data validation to ensure the cells in the range are either 5 or 9
digits in length; however, I don't know how to set the formatting of the cell
to recognize whether to use the Zip Code format or the ZIP Code +4 format.

Can I get both?

formatting a cell for both ZIP and ZIP+4
 

Post the formulas you're using to ensure a 5 or 9 digit entry.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"dandiehl" wrote in message
...
I am using data validation to ensure the cells in the range are either 5 or
9
digits in length; however, I don't know how to set the formatting of the
cell
to recognize whether to use the Zip Code format or the ZIP Code +4 format.

Can I get both?

formatting a cell for both ZIP and ZIP+4
 

Maybe with something like this..........

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Len(Target) = 5 Then
Selection.NumberFormat = "00000"
Else
If Len(Target) = 9 Then
Selection.NumberFormat = "00000-0000"
Else
End If
End If
End Sub

Vaya con Dios,
Chuck, CABGx3


"dandiehl" wrote in message
...
I am using data validation to ensure the cells in the range are either 5
or 9
digits in length; however, I don't know how to set the formatting of the
cell
to recognize whether to use the Zip Code format or the ZIP Code +4 format.

Can I get both?

formatting a cell for both ZIP and ZIP+4
 

Try a custom format of
Format|Cells|Number tab|Custom category
[99999]00000-0000;00000

dandiehl wrote:

I am using data validation to ensure the cells in the range are either 5 or 9
digits in length; however, I don't know how to set the formatting of the cell
to recognize whether to use the Zip Code format or the ZIP Code +4 format.

Can I get both?

--

Dave Peterson

formatting a cell for both ZIP and ZIP+4
 

Thank you all for your help!

"Dave Peterson" wrote:

Try a custom format of
Format|Cells|Number tab|Custom category
[99999]00000-0000;00000

dandiehl wrote:

I am using data validation to ensure the cells in the range are either 5 or 9
digits in length; however, I don't know how to set the formatting of the cell
to recognize whether to use the Zip Code format or the ZIP Code +4 format.

Can I get both?

--

Dave Peterson