Bahut bahut dhanyawad,anandji, but negative number is not appearing in brackets?
Bahut bahut dhanyawad,anandji, but negative number is not appearing in brackets?
How nice, an answer to the question that no-one asked.Originally Posted by anandbohra
Anand, when you post code, make sure you use the VBA tags - if you paste your code into the post, select it and then click on the "VBA" button, as this makes it easier to read.
OK sir
Next time i will keep this in mind
Dear AnandOriginally Posted by anandbohra
You have written a excellent VBA
Only one query,
Is it possible to show in the same code negative numbers within bracket and in red colour.
If that can be done it will be excellent.
Please accept my thanks in advance
Regards,
you can also change the regional settings to this format...from the control panel
Sir,
What if value is updated. For example If value is decreased from Lakhs to Thousand then it is showing ,00,000 we have to again run macro code..?
@dhawal, This thread is 13 years old. Could you please start a new thread?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
if value is changed (decreased from crores to thousand) then it is showing value like this
,,00,000.
for example if value before running above code is 2,35,25,367 and it is changed to 25,367 it is showing value like this ,,25,367 instead of 25,367
is it possible to updated value formatting as and when it is changed.
Sub Indianrupees() On Error GoTo Anand: Dim rcell As Range Dim rrupeerange As Range Set rcell = ActiveCell Set rrupeerange = Application.InputBox(prompt:="Select a cell or a Range ", Type:=8, _ Default:=Selection.Address) areacount = rrupeerange.Cells.Count If areacount < 65000 Then Application.StatusBar = "Wait while System Converts into Indian Rupee Format....!" Application.ScreenUpdating = False For Each rcell In rrupeerange Select Case rcell.Value Case Is >= 1E+15 rcell.Cells.NumberFormat = _ "##"",""00"",""00"",""00"",""00"",""00"",""00"",""000.00" Case Is >= 10000000000000# rcell.Cells.NumberFormat = _ "##"",""00"",""00"",""00"",""00"",""00"",""000.00" Case Is >= 100000000000# rcell.Cells.NumberFormat = _ "##"",""00"",""00"",""00"",""00"",""000.00" Case Is >= 1000000000 rcell.Cells.NumberFormat = "##"",""00"",""00"",""00"",""000.00" Case Is >= 10000000 rcell.Cells.NumberFormat = "##"",""00"",""00"",""000.00" Case Is >= 100000 rcell.Cells.NumberFormat = "##"",""00"",""000.00" Case Else rcell.Cells.NumberFormat = "##,###.00" End Select Next rcell Application.ScreenUpdating = True Set rcell = Nothing Set rrupeerange = Nothing Application.StatusBar = "" Else a = MsgBox("You had selected Total " & areacount & " Cells, Want to Proceed ? ", _ vbYesNo, "Anand M. Bohra") If a = vbYes Then Application.StatusBar = "Wait while System Converts into Indian Rupee Format....!" Application.ScreenUpdating = False For Each rcell In rrupeerange Select Case rcell.Value Case Is >= 1E+15 rcell.Cells.NumberFormat = _ "##"",""00"",""00"",""00"",""00"",""00"",""00"",""000.00" Case Is >= 10000000000000# rcell.Cells.NumberFormat = _ "##"",""00"",""00"",""00"",""00"",""00"",""000.00" Case Is >= 100000000000# rcell.Cells.NumberFormat = _ "##"",""00"",""00"",""00"",""00"",""000.00" Case Is >= 1000000000 rcell.Cells.NumberFormat = "##"",""00"",""00"",""00"",""000.00" Case Is >= 10000000 rcell.Cells.NumberFormat = "##"",""00"",""00"",""000.00" Case Is >= 100000 rcell.Cells.NumberFormat = "##"",""00"",""000.00" Case Else rcell.Cells.NumberFormat = "##,###.00" End Select Next rcell Application.ScreenUpdating = True Set rcell = Nothing Set rrupeerange = Nothing Application.StatusBar = "" Else MsgBox "Select Smaller data for Faster Formatting", vbInformation, "Anand M. Bohra" End If End If Exit Sub
if value is changed (decreased from crores to thousand) then it is showing value like this
,,00,000.
for example if value before running above code is 2,35,25,367 and it is changed to 25,367 it is showing value like this ,,25,367 instead of 25,367
is it possible to updated value formatting as and when it is changed.
Last edited by Aussiebear; 04-07-2023 at 01:09 PM. Reason: Added code tags to supplied code
Dhwal, as I have previously indicated this thread is over 13 years old. Please start a new one?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link