Formatting for variable decimals
Formatting for variable decimals
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,579
    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,579
    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

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
  •  

 

 
DMCA.com