Many thanks all!
I will try the vb approach.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Dim c As Range
Set aoi = [a1:a100]

For Each c In aoi
Select Case c.Value
Case Is = 0.5 * 10 ^ 12
c.NumberFormat = "0.00,,,, \T\B"
Case Is = 0.5 * 10 ^ 9
c.NumberFormat = "0.00,,,\G\B"
Case Is = 0.5 * 10 ^ 6
c.NumberFormat = "0.00,,\M\B"
Case Is = 0.5 * 10 ^ 3
c.NumberFormat = "0.00,\K\B"
Case Else
c.NumberFormat = "General"
End Select
Next c

End Sub

A question here ... Why did you choose a1:a100 is this just a hypothetical
location? I guess I was thinking that I could integrate my own 'function'
in VB into excel... Something like Date, which keeps the underlying value
but modifies the display properties.

If I understand your example then when excel is ready to redisplay the
worksheet
your Worksheet_Change function gets called. But I assume it only gets
called
for the ragne you specify. (I know VB well I'm just not used to using it in
excel,
if you know what I mean.)



////////////////////////////////////////////////////////////////////////////
// At the moment my routine is in c++ and looks like this:
// I will translate this to its VB equivalent...
////////////////////////////////////////////////////////////////////////////
double KMGT::intpart(double dv)
{
double di(0);
modf(dv, &di);
return(di);
}
ostream & operator<<(ostream & o, const KMGT & n_)
{
using namespace std;
double k, m, g, t;

k = n_.n / 1024.;
m = n_.n / 1048576.;
g = n_.n / 1073741824.;
t = n_.n / 1099511627776.;

int p = o.precision();
ios_base::fmtflags f = o.flags();

o << setprecision(1) << fixed;
if (n_.intpart(t) != 0)
o << t << "TB";
else if (n_.intpart(g) != 0)
o << g << "GB";
else if (n_.intpart(m) != 0)
o << m << "MB";
else if (n_.intpart(k) != 0)
o << k << "KB";
else
o << n_.n << "B";
o.precision( p );
o.flags( f );
return(o);
}