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.

Continue reading →