Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Formatting for variable decimals

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Australia
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I know with conditional formatting you can change colours and fonts, but is there a way of changing the number of figures after the decimal point. For example, if my number is less than 100 I want two significant figures. If it is greater than 100 but less than 1000 I want one, and for greater than 1000 I don't want anything after the decimal point:
    eg 99.99
    999.9
    9999

    Thanks,

    Penfold

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Penfold

    Don't think you can achieve this with conditional formatting. You could try this event macro though:
    Right click your sheet tab, left click View Code, then paste in this code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value < 100 Then
    Target.NumberFormat = "0.00"
    Else
    If Target.Value < 1000 Then
    Target.NumberFormat = "0.0"
    Else
    If Target.Value >= 1000 Then
    Target.NumberFormat = "0"
    End If
    End If
    End If
    End Sub

    This appears to round up (ie 99.995 becomes 100.00) but may help you.

    regards
    Derek

    [ This Message was edited by: Derek on 2002-03-11 00:51 ]

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi


    I was beaten to it but here is mine anyway.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strFormat As String
    If Target.Cells.Count > 1 Then End
    If Not IsNumeric(Target) Then End

    Select Case Target
    Case Is < 100
    strFormat = "0.00"
    Case Is < 1000
    strFormat = "0.0"
    Case Is > 9999
    strFormat = "0"
    End Select

    Target.NumberFormat = strFormat

    End Sub


  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Australia
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Derek, thats almost exactly what I want except that I have a large range of cells which I want to apply this to (rather than one by one). I guess I can use a similar process via VBA to do this or alternatively add an IF(a1<100,trunc(a1,2)...etc) statement to each cell but I was hoping that the magic of conditional formatting would solve all of my problems.

    Cheers,

    Penfold

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again

    Paste this code into a standard macro module, select the area you want to change then run this macro. This will format all your existing cells. After this the event macro I gave you before (or the one from Dave Hawley) will format any new entries you make, as you make them.

    For Each cell In Selection
    If cell.Value < 100 Then
    cell.NumberFormat = "0.00"
    Else
    If cell.Value < 1000 Then
    cell.NumberFormat = "0.0"
    Else
    If cell.Value >= 1000 Then
    cell.NumberFormat = "0"
    End If
    End If
    End If
    Next
    End Sub

    regards
    Derek

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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