Maybe...
If the numbers change because of user input, you could use an event macro.
Rightclick on the worksheet tab that should have this behavior and select view
code.
Paste this in:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myNumberFormat As String
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("b:b")) Is Nothing Then Exit Sub
If Application.IsNumber(Target.Value) = False Then Exit Sub
myNumberFormat = ""
Select Case Target.Value
Case Is < 100000
myNumberFormat = "#,###.00"
Case Is < 1000000
myNumberFormat = "##\,##\,##0.00"
Case Is < 100000000
myNumberFormat = "##\,##\,##\,##0.00"
End Select
If myNumberFormat = "" Then
'you didn't make enough formats!
Else
Target.NumberFormat = myNumberFormat
End If
End Sub
Add as many checks as you want.
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
If you want to read more about these kinds of events:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm
David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm
SVC wrote:
>
> Is there any macro through which we can achive the same.
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > That is difficult, because the format only allows a certain number of
> > conditions, and we have exhausted them.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "SVC" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Dear Phillips,
> >>
> >> my requirement is as below
> >> no matter what the number is it should display 2 decimal places & then
> > going
> >> from left to right 1st 3 digits left alone after which there should be
> > comma
> >> after every 2 digits.
> >>
> >> eg:
> >> 100 = 100.00
> >> 1000 = 1,000.00
> >> 10000 = 10,000.00
> >> 100000 = 1,00,000.00
> >> 1000000 = 10,00,000.00
> >> 10000000 = 1,00,00,000.00
> >> 100000000 = 10,00,00,000.00
> >> 1000000000 = 1,00,00,00,000.00
> >> 10000000000 = 10,00,00,00,000.00
> >> ..... & so on
> >> "Bob Phillips" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > Decimal points
> >> >
> >> > [>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00
> >> >
> >> > All the hashes represent a number, but show as blank if they are
> >> > leading
> >> > zeroes. With normal numbers, you just define
> >> >
> >> > #,##0.00
> >> >
> >> > and Excel will add a comma every 3rd digit, but as Indian numbers have
> >> > commas in 2nd digit in some cases, it all has to be explicitly defined.
> >> >
> >> > --
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > (remove nothere from email address if mailing direct)
> >> >
> >> > "SVC" <(E-Mail Removed)> wrote in message
> >> > news:(E-Mail Removed)...
> >> >> How to add decimial points & can you explain this sequence of hash &
> >> >> zeros
> >> >>
> >> >> "Bob Phillips" <(E-Mail Removed)> wrote in message
> >> >> news:(E-Mail Removed)...
> >> >> > Use a custom format of
> >> >> >
> >> >> > [>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0.00;##,##0
> >> >> >
> >> >> > --
> >> >> > HTH
> >> >> >
> >> >> > Bob Phillips
> >> >> >
> >> >> > (remove nothere from email address if mailing direct)
> >> >> >
> >> >> > "SVC" <(E-Mail Removed)> wrote in message
> >> >> > news:%(E-Mail Removed)...
> >> >> >> Hi!
> >> >> >>
> >> >> >> sample number 123456789
> >> >> >> In excel normally numbers are seprated in 1000's format ie
> >> > 123,456,789.00
> >> >> >>
> >> >> >> I NEED IT IN this format (indian style of rupees) 12,34,56,789
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
--
Dave Peterson
Similar Threads
