Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Mar 2006
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format Cells in Excel (Excel 2003)

    Would like to format cells over a large range to roundup to the next number with no decimal points. Do I have to create a formula for each of these cells or can I get it to work when I key in the number into the cell?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format Cells in Excel (Excel 2003)

    If you can live with changing all cells at once, you can run this macro:

    Sub RoundEmUp()
    Dim oCell As Range
    For Each oCell In Range("A1:H20").Cells
    If Not oCell.HasFormula And IsNumeric(oCell) Then
    oCell = -Int(-oCell)
    End If
    Next oCell
    End Sub

    Change the range as needed. You can assign this macro to a toolbar button and/or keyboard shortcut.

    If you would like to change the values as they are entered, right-click the sheet tab, select View Code from the popup menu and enter or post the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Range("A1:H20"), Target) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Range("A1:H20"), Target).Cells
    If Not oCell.HasFormula And IsNumeric(oCell) Then
    oCell = -Int(-oCell)
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

    Change the range as needed.

    Notes:
    1) This event procedure will disable Undo!
    2) If you need to be able to enter times within the specified range, the code would have to be modified.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •