vba help - Number format

[Mallesh23]

Hi Team,

I am using someones code for converting amounts column into Accounting format.

Are there any other way to get output with simple code.

this is very lengthy :=> "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"

Sub Convert_Numberformat()
Range("C2:C15").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"

End Sub

Column A is input Data and Column C and E is expected output. with decimal and without decimal

Book4
	A	B	C	D	E
1	Amount		Expected with Decimal		Expected without decimal
2	294376		2,94,376.00		2,94,376
3	767364		7,67,364.00		7,67,364
4	1378453		13,78,453.00		13,78,453
5	1421642		14,21,642.00		14,21,642
6	801295		8,01,295.00		8,01,295
7	964148		9,64,148.00		9,64,148
8	427503		4,27,503.00		4,27,503
9	158313		1,58,313.00		1,58,313
10	871476		8,71,476.00		8,71,476
11	1389822		13,89,822.00		13,89,822
12	1020023		10,20,023.00		10,20,023
13	1057490		10,57,490.00		10,57,490
14	777667		7,77,667.00		7,77,667
Sheet1

Thanks
mg

 

[hrayani]

Sub Convert_Numberformat()
Range("C2:C15").NumberFormat = "#,#.00"
End Sub

 

[Mallesh23]

Hi hrayani,

Thanks for your help, The code is working , giving correct output.

With decimal :=>Range("C2:C15").NumberFormat = "#,#.00"
Without decimal :=>Range("C2:C15").NumberFormat = "#,#"

What will be the code if I want Negative Number to be surrounded by bracket.
instead of negative symbol.


Thanks
mg

 

[hrayani]

Hi MG,

This will turn the negative numbers surrounded by brackets

Sub Convert_Numberformat()
Range("C2:C15").NumberFormat = "#,#.00;(#,#.00)"
End Sub

If you want the negative numbers to turn red besides being surrounded by brackets

Sub Convert_Numberformat()
Range("C2:C15").NumberFormat = "#,#.00;[RED](#,#.00)"
End Sub

Regards,

Humayun

 

[Mallesh23]

Hi Humayun,

Thanks for your help. its working.


Thanks
mg

 

[hrayani]

Welcome friend
Thanks for the feedback
Take Care