I need to custom format a cell to read 00,00,000, instead of the usual comma
after three spaces.

This is for an application that uses Rupees, which have a comma break at
1,000, then the next 100, and then another 100. In other words, whereas 10M
would normally print "10,000,000", I need it to print "100,00,000". Further,
I need every 1,000 after this to be at the three digit mark. In other words,
1B would need to print as "100,000,00,00,000". The key here is to get a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000 to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number. If
anyone can give me the custom field text for formatting, would be greatly
appreciated.

Thx.
--
Boris

[=10000000]"Rs."##\,##\,##\,##0.00;[=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break at
1,000, then the next 100, and then another 100. In other words, whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000 to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number. If
anyone can give me the custom field text for formatting, would be greatly
appreciated.

Thx.
--
Boris

Bob, thanks. I had this working the past few days, and then just came across
a negative value, which is not adhering to these formatting standards. Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


"Bob Phillips" wrote:

[=10000000]"Rs."##\,##\,##\,##0.00;[=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break at
1,000, then the next 100, and then another 100. In other words, whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000 to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number. If
anyone can give me the custom field text for formatting, would be greatly
appreciated.

Thx.
--
Boris

I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[=10000000]""Rs.""##\,##\,##\,##0.00;[=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
Bob, thanks. I had this working the past few days, and then just came
across
a negative value, which is not adhering to these formatting standards.
Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


"Bob Phillips" wrote:


[=10000000]"Rs."##\,##\,##\,##0.00;[=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break
at
1,000, then the next 100, and then another 100. In other words,
whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get
a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000
to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number.
If
anyone can give me the custom field text for formatting, would be
greatly
appreciated.

Thx.
--
Boris

I've not had experience with worksheet code. I guess what I'm not clear on
is how this knows what to apply this formatting to. I don't want everything
to be this formatting.
--
Boris


"Bob Phillips" wrote:

I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[=10000000]""Rs.""##\,##\,##\,##0.00;[=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
Bob, thanks. I had this working the past few days, and then just came
across
a negative value, which is not adhering to these formatting standards.
Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


"Bob Phillips" wrote:


[=10000000]"Rs."##\,##\,##\,##0.00;[=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break
at
1,000, then the next 100, and then another 100. In other words,
whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get
a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000
to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number.
If
anyone can give me the custom field text for formatting, would be
greatly
appreciated.

Thx.
--
Boris

Bob gave instructions on how to insert the code with this:

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


And this is the line that should change to the range you want to use:

Const WS_RANGE As String = "H1:H10" '<=== change to suit

Just type the address you want in place of H1:H10.

BorisS wrote:

I've not had experience with worksheet code. I guess what I'm not clear on
is how this knows what to apply this formatting to. I don't want everything
to be this formatting.
--
Boris

"Bob Phillips" wrote:

I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[=10000000]""Rs.""##\,##\,##\,##0.00;[=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
Bob, thanks. I had this working the past few days, and then just came
across
a negative value, which is not adhering to these formatting standards.
Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


"Bob Phillips" wrote:


[=10000000]"Rs."##\,##\,##\,##0.00;[=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break
at
1,000, then the next 100, and then another 100. In other words,
whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get
a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000
to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number.
If
anyone can give me the custom field text for formatting, would be
greatly
appreciated.

Thx.
--
Boris







--

Dave Peterson

so this is only for a specific range, as opposed to being something I can
apply as a regular, custom format. Am I understanding correctly?
--
Boris


"Dave Peterson" wrote:

Bob gave instructions on how to insert the code with this:

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


And this is the line that should change to the range you want to use:

Const WS_RANGE As String = "H1:H10" '<=== change to suit

Just type the address you want in place of H1:H10.

BorisS wrote:

I've not had experience with worksheet code. I guess what I'm not clear on
is how this knows what to apply this formatting to. I don't want everything
to be this formatting.
--
Boris

"Bob Phillips" wrote:

I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[=10000000]""Rs.""##\,##\,##\,##0.00;[=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
Bob, thanks. I had this working the past few days, and then just came
across
a negative value, which is not adhering to these formatting standards.
Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


"Bob Phillips" wrote:


[=10000000]"Rs."##\,##\,##\,##0.00;[=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break
at
1,000, then the next 100, and then another 100. In other words,
whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get
a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000
to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number.
If
anyone can give me the custom field text for formatting, would be
greatly
appreciated.

Thx.
--
Boris







--

Dave Peterson

Yes.

BorisS wrote:

so this is only for a specific range, as opposed to being something I can
apply as a regular, custom format. Am I understanding correctly?
--
Boris

"Dave Peterson" wrote:

Bob gave instructions on how to insert the code with this:

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


And this is the line that should change to the range you want to use:

Const WS_RANGE As String = "H1:H10" '<=== change to suit

Just type the address you want in place of H1:H10.

BorisS wrote:

I've not had experience with worksheet code. I guess what I'm not clear on
is how this knows what to apply this formatting to. I don't want everything
to be this formatting.
--
Boris

"Bob Phillips" wrote:

I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[=10000000]""Rs.""##\,##\,##\,##0.00;[=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
Bob, thanks. I had this working the past few days, and then just came
across
a negative value, which is not adhering to these formatting standards.
Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


"Bob Phillips" wrote:


[=10000000]"Rs."##\,##\,##\,##0.00;[=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break
at
1,000, then the next 100, and then another 100. In other words,
whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get
a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000
to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number.
If
anyone can give me the custom field text for formatting, would be
greatly
appreciated.

Thx.
--
Boris







--

Dave Peterson


--

Dave Peterson

Yes, but you can keep adding ranges to the target range, such as

Const WS_RANGE As String = "H1:H10,M1:M5,O17"

etc.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
so this is only for a specific range, as opposed to being something I can
apply as a regular, custom format. Am I understanding correctly?
--
Boris


"Dave Peterson" wrote:

Bob gave instructions on how to insert the code with this:

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


And this is the line that should change to the range you want to use:

Const WS_RANGE As String = "H1:H10" '<=== change to suit

Just type the address you want in place of H1:H10.

BorisS wrote:

I've not had experience with worksheet code. I guess what I'm not
clear on
is how this knows what to apply this formatting to. I don't want
everything
to be this formatting.
--
Boris

"Bob Phillips" wrote:

I don't think that is possible as the format cannot handle that
number of
conditions (2 for positive, 2 for negative and default). What you
could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _


"[=10000000]""Rs.""##\,##\,##\,##0.00;[=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _


"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
Bob, thanks. I had this working the past few days, and then just
came
across
a negative value, which is not adhering to these formatting
standards.
Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


"Bob Phillips" wrote:



[=10000000]"Rs."##\,##\,##\,##0.00;[=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"BorisS" wrote in message
...
I need to custom format a cell to read 00,00,000, instead of
the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a
comma break
at
1,000, then the next 100, and then another 100. In other
words,
whereas
10M
would normally print "10,000,000", I need it to print
"100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark.
In other
words,
1B would need to print as "100,000,00,00,000". The key here
is to get
a
comma between the uptick from 99,000 to 100,000, so that it
shows up
1,00,000; and the next comma to show between the uptick from
9,999,000
to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the
number.
If
anyone can give me the custom field text for formatting, would
be
greatly
appreciated.

Thx.
--
Boris







--

Dave Peterson