I was given a project, recently, that involved creating a spreadsheet to list storage capacities and free space. One thing I wasn’t sure of, was how to format the values.
A bit of searching on the internet for formatting KB MB, etc. came up with a post by Ajay on http://www.eggheadcafe.com which suggested pasting the following code into the current worksheet’s private module:
Private Sub Worksh5eet_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
I had some trouble with the macro and eventually abandoned it, but it did give me some ideas. The actual formats were good and I ended up using them later. The criteria for setting the formatting was spot on as well. After thinking about it for a while, I decided to look into Excel’s conditional formatting feature. There are premade rules for changing the cell color, etc., but it turns out that it’s capable of quite a bit more.