Macro to Format Values a Standard Excel Option

[tatertot]

I am wanting to format 3 columns of data in the comma style like I would if I used the Alt+H+K shortcut would format. Instead I cannot find any method that works. I have used "#,###.##" & "Comma" and still the values do not look at all like they would if I used the comma icon on the Home ribbon.

Here is my code:
Worksheets("AUC").Range("L:N").numberformat = "#,##.##"

 

[Fishboy]

I am wanting to format 3 columns of data in the comma style like I would if I used the Alt+H+K shortcut would format. Instead I cannot find any method that works. I have used "#,###.##" & "Comma" and still the values do not look at all like they would if I used the comma icon on the Home ribbon.

Here is my code:
Worksheets("AUC").Range("L:N").numberformat = "#,##.##"

Hi tatertot, welcome to the boards.

That is a curious issue as generally your code works for me. This is my test data before running the macro:

Excel 2010
	L	M	N
1	1000.57	1000.57	1000.57
2	1000.57	1000.57	1000.57
3	1000.57	1000.57	1000.57
4	1000.57	1000.57	1000.57
5	1000.57	1000.57	1000.57
6	1000.57	1000.57	1000.57
7	1000.57	1000.57	1000.57
8	1000.57	1000.57	1000.57
9	1000.57	1000.57	1000.57
10	1000.57	1000.57	1000.57
11	1000.57	1000.57	1000.57
12	1000.57	1000.57	1000.57
13	1000.57	1000.57	1000.57
14	1000.57	1000.57	1000.57
15	1000.57	1000.57	1000.57
16	1000.57	1000.57	1000.57
17	1000.57	1000.57	1000.57
AUC

Here is my (slightly modified) version of your code:

Sub UpdateCellFormat()
Sheets("AUC").Range("L:N").NumberFormat = "#,###.##"
End Sub

And here is what the end result looked like:

Excel 2010
	L	M	N
1	1,000.57	1,000.57	1,000.57
2	1,000.57	1,000.57	1,000.57
3	1,000.57	1,000.57	1,000.57
4	1,000.57	1,000.57	1,000.57
5	1,000.57	1,000.57	1,000.57
6	1,000.57	1,000.57	1,000.57
7	1,000.57	1,000.57	1,000.57
8	1,000.57	1,000.57	1,000.57
9	1,000.57	1,000.57	1,000.57
10	1,000.57	1,000.57	1,000.57
11	1,000.57	1,000.57	1,000.57
12	1,000.57	1,000.57	1,000.57
13	1,000.57	1,000.57	1,000.57
14	1,000.57	1,000.57	1,000.57
15	1,000.57	1,000.57	1,000.57
16	1,000.57	1,000.57	1,000.57
17	1,000.57	1,000.57	1,000.57
AUC

What is happening at your end? What does it actually look like when you say "do not look at all like they would if I used the comma icon"?

 

[tatertot]

Hi Fishboy,

What is happening is where there is not values. In those cells, that format is not matching what would happen when I use the "comma" format in Excel. When I use that standard format from Excel options, the blank cells get "-" for 0's. What code would be exactly what would happen if I used the comma option in Excel?

 

[Fishboy]

Hi Fishboy,

What is happening is where there is not values. In those cells, that format is not matching what would happen when I use the "comma" format in Excel. When I use that standard format from Excel options, the blank cells get "-" for 0's. What code would be exactly what would happen if I used the comma option in Excel?

Hi tatertot,

I am not entirely I certain I follow. Do you mean that you want with zero or blank values to have a "-" prefix? Can you show me what you expect the final result to look like for:

1. A blank cell?
2. A cell with a zero value?
3. A cell with a positive value?