custom number format alignment

[dim4x4]

does anyone know if it's possible to make a custom number format which would right align positive and negative numbers, but center zeros and text? thx.

 

[Tom Urtis]

You can probably do it using VBA such as with the example below. Right click on your sheet tab, left click on View Code, and paste this in. Modify for range of interest.

You did not specify if your data is manually entered, or if it appears in cells as the result of a formula, or if you already have the data in thousands of cells and now you need to do a mass-format.

This assumes your data is manually entered, and the formatting will happen as you go. If I guessed wrong, please repost.

'''''''''''''''''

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("A1:D10")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If Target.Value = 0 Or Application.IsText(Target.Value) = True Then
Target.HorizontalAlignment = xlCenter
Else
Target.HorizontalAlignment = xlRight
End If
End Sub

''''''''''''''''''''''

 

[dim4x4]

Thanks Tom!