Put this in ThisWorkbook module
[VBA]
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Sh.Range("A1:G100")) Is Nothing Then Exit Sub

Dim c
If Target.Cells.Count = 1 Then
Select Case Target.Value
Case Is >= 1000000000
Target.Cells.NumberFormat = "##"",""00"",""00"",""00"",""000.00"
Case Is >= 10000000
Target.Cells.NumberFormat = "##"",""00"",""00"",""000.00"
Case Is >= 100000
Target.Cells.NumberFormat = "##"",""00"",""000.00"
Case Else
Target.Cells.NumberFormat = "##,###.00"
End Select
Else
For Each c In Intersect(Target, Sh.Range("A1:G100"))
Select Case c.Value
Case Is >= 1000000000
c.NumberFormat = "##"",""00"",""00"",""00"",""000.00"
Case Is >= 10000000
c.NumberFormat = "##"",""00"",""00"",""000.00"
Case Is >= 100000
c.NumberFormat = "##"",""00"",""000.00"
Case Else
c.NumberFormat = "##,###.00"
End Select
Next c
End If
End Sub

[/VBA]