Solved

Excel Custom number format

Posted on 2013-06-04
6
579 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 48

Expert Comment

by:Rgonzo1971
Comment Utility
HI,

What should be shown if negative numbers?

Regards
0
 

Author Comment

by:GRChandrashekar
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

Expert Comment

by:barry houdini
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now