Sub FormatCell()
  For Each cell In Selection
    If IsNumeric(cell.Value) Then
      If Abs(cell.Value) > 100 Then
        cell.NumberFormat ="#"
      ElseIf Abs(cell.Value) > 10 Then
        cell.NumberFormat ="0.#"
      ElseIf Abs(cell.Value) > 1 Then
        cell.NumberFormat ="0.##"
      ElseIf Abs(cell.Value) > 0.1 Then
        cell.NumberFormat ="0.###"
      ElseIf Abs(cell.Value) > 0.01 Then
        cell.NumberFormat ="0.####"
      ElseIf Abs(cell.Value) > 0.001 Then
        cell.NumberFormat = "0.#####"
      ElseIf Abs(cell.Value) > 0.0001 Then
        cell.NumberFormat = "#.#####"
      Else
        cell.NumberFormat = "[red] general"
    End If
  Next cell
End Sub

Sub FormatCell2()
  For Each cell In Selection
    If IsNumeric(cell.Value) Then
Select Case cell.Value
    	Case Is > 100
        cell.NumberFormat = "#"
    Case Is > 10
        cell.NumberFormat = "0.#"
    Case Is > 1
        cell.NumberFormat = "0.##"
    Case Is > 0.1
        cell.NumberFormat = "0.###"
    Case Is > 0.01
        cell.NumberFormat = "0.####"
    Case Is > 0.001
        cell.NumberFormat = "0.#####"
    Case Is > 0.0001
        cell.NumberFormat = "#.#####"
    Case Else
        cell.NumberFormat = "[red] general"
End Select
    End If
  Next cell
End Sub