Solved

Excel Custom number format

Posted on 2013-06-04
6
606 Views
Last Modified: 2013-06-06
I have the following custom format in excel.

[>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0

But this does not show -ve values in brackets and formatting  if value is -ve is not correct. but if positive it works correctly.

Please help
0
Comment
Question by:GRChandrashekar
6 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39221184
HI,

What should be shown if negative numbers?

Regards
0
 

Author Comment

by:GRChandrashekar
ID: 39221203
it should be like (1,57,300)
now it shows -157,300
but positive shows correct 1,57,300
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39221675
I'm not sure you can add another format to your existing custom format - you can use conditional formatting to get the correct format for negative numbers, are you interested in doing that?

regards, barry
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:GRChandrashekar
ID: 39221830
can we re-write the custom format and accommodate this ?
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39221918
No I don't think you can because when you start using >100000 etc. there's a limit to how many you can use (and I think you've reached it), what should be the format for <100000 but positive (is that possible)? Are all negative numbers formatted the same way or do you still want the same distinction as with positive?

regards, barry
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 39223760
You could try this.  Assuming your values are in Column A...

1) Put this code in the sheet module for the sheet you need "watched"

Private Sub Worksheet_Calculate()
    
    Dim LastR As Long
    Dim Counter As Long
    
    Application.EnableEvents = False
    
    With Me
        LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
        For Counter = 1 To LastR
            If .Cells(Counter, "a") >= 0 Then
                cel.NumberFormat = "[>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0"
            Else
                cel.NumberFormat = "[<=-10000000](##\,##\,##\,##0);[<=-100000](##\,##\,##0);##,##0"
            End If
        Next
    End With
    
    Application.EnableEvents = True
    
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim Updated As Range
    Dim cel As Range
    
    Set Updated = Intersect(Me.Range("a:a"), Target)
    If Not Updated Is Nothing Then
        Application.EnableEvents = False
        For Each cel In Updated.Cells
            If cel >= 0 Then
                cel.NumberFormat = "[>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0"
            Else
                cel.NumberFormat = "[<=-10000000](##\,##\,##\,##0);[<=-100000](##\,##\,##0);##,##0"
            End If
        Next
        Application.EnableEvents = True
    End If
    
End Sub

Open in new window


2) Now, if you manually change cell values in column A, or the worksheet recalculates, the different event subs will apply the custom number format on a case by case basis
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question