excel - "Unable to set the NumberFormat property of the Range class"

Question

This code has been working for ages. I thought maybe I accidently pressed a key but I cannot seem to see it. I suddenly get the error:

Unable to set the NumberFormat property of the Range class

in the below code:

Worksheets("Sheet1").Cells(y + k, x + j).Select
TempValue = Worksheets("Sheet1").Cells(y + k, x + j).Value
Worksheets("Sheet1").Cells(y + k, x + j).NumberFormat = "#,##0" //ERROR HERE
Worksheets("Sheet1").Cells(y + k, x + j).Value = TempValue

Answer 1

The problem as discovered in Chat was the workbook had more than 64,000 formats because of which the user was getting the "Too many different cell formats" error message in Excel

Solution

Topic: You receive a "Too many different cell formats" error message in Excel

Link: http://support.microsoft.com/kb/213904

QUOTE from the above link

In Microsoft Excel 2007/2010 files may produce the following error message;

Excel found unreadable content in the file

This problem occurs when the workbook contains more than approximately 4,000 different combinations of cell formats in Excel 2003 or 64,000 in Excel 2007 and higher.

Solution as mentioned in the link is to download and run the XLStyesTool