Formatting Cells

Posted by Anand on June 12, 2001 4:24 AM

Hi.

I live in India. Here numbers are displayed not in millions but in lakhs (100,000) and crores (10 millions)

For instance 19,999,999 would appear as 1,99,99,999 and 1,999,999,999 would appear as 1,99,99,99,999 so on

How do I format the cells to display numbers in this fashion

Thanks and regards

Posted by Joe Was on June 12, 2001 7:11 AM

This is the "Sheet Tab code" to get values in a format. You paste it in the "View Code" item, Right click on the Sheet Tab and select "View Code" then paste this code in. Do not change the Sub name or the code will fail.

Excel will not do a mixed format! if you format the first four digits to #,### then all other digits must be in this form! So the code above will only format a comma every third place even though it is mixed to format six places and above to two place commas.

Excel indicates that the windows "Local Settings" do change how the format works. The test system is English/U.S. so if you change your system local settings to one of the Indian forms it may work? JSW

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
Target = Format(Target, "#,##,##0")
Application.EnableEvents = True
End Sub