Solved

Excel Custom number format

Posted on 2013-06-04
6
624 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 50

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

696 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