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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
How to add decimial points & can you explain this sequence of hash &
zeros

"Bob Phillips" wrote in message
...
Use a custom format of

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SVC" wrote in message
...
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