Asked By Rick Rothstein
12-Jan-09 02:36 PM
When you copied that entire text (verbatim, by the way) from when I
originally posted that solution on December 14, 2008 (subject line
sentence...
numbers and execute this code from the Immediate Window..."
doesn't mean much unless you include the last line I originally posted,
namely, this line...
Selection.Formula = Selection.Formula
--
Rick (MVP - Excel)
You can't do that with straight cell formatting, but you can implement
it
using event code. Right click the tab at the bottom of the worksheet
and
select View Code from the popup menu that appears, then copy/paste
the
following into the code window that opened up...
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
If Target.Value < 1000 Then
Target.NumberFormat = "0 \B"
ElseIf Target.Value < 999500 Then
Target.NumberFormat = "0.000, \K\B"
ElseIf Target.Value < 999500000 Then
Target.NumberFormat = "0.000,, \M\B"
ElseIf Target.Value < 999500000000# Then
Target.NumberFormat = "0.000,,, \G\B"
Else
Target.NumberFormat = "0.000,,,, \T\B"
End If
End If
End Sub
Since you didn't tell us what cells, I assumed Column "C" (change the
Range
inside the Intersect function to the cell range you want to have this
functionality). After doing this, any numbers entered into those cells
will
adopt the number format you requested. Note that existing numbers will
not
change unless re-entered. You can do that one at a time or you can
select
all the existing numbers and execute this code from the Immediate
Window...