Too Many Cell Formats!

Does anyone understand this hideous hideous bug in Excel 2003 or more modern versions?

Thanks

>>Does anyone understand "Too Many Cell Formats" error message

Microsoft does.

If you have more than 4,000 different kinds of cell format in an Excel 2003 application (64,000 in later versions) it uses up allocated resources and the application freaks out.

Simplify! There are some tools to do so (an add-in etc) in the linked support question.

thanks for the link and I have read it but it doesn't solve the problem. Because I have highlighted the entire workbook, pressed ctrl 1, set all cells to the same format, and cleared all the colours, yet it still cells too many formats! ridiculous! Any ideas? thanks

none, only numbers is all

I will try to explain this to the best of my knowledge. Apparently when you switch version of excel 2003 to 2007 etc. it corrupts the formats. So removing formatting is futile. Each time you copy the corrupt formats continue to stack. Once you reach the limit of styles/formats it prevents you from copying or adding additional formating and make make your file crash.

I have an add in that I found on the web that I can no longer find the link. This add-in was originally created by Andrew Keeler for non commercial purposes. This add-in removes corrupt styles. This has worked wonders for me but I can not claim credit for it. All Thanks should go to Andrew!!

I can not seem to attach an .xlam file so here is the code.

Sub RemoveTheStyles(control As IRibbonControl)
Dim s As Style, i As Long, c As Long
On Error Resume Next
If ActiveWorkbook.MultiUserEditing Then
If MsgBox("You cannot remove Styles in a Shared workbook." & vbCr & vbCr & _
"Do you want to unshare the workbook?", vbYesNo + vbInformation) = vbYes Then
ActiveWorkbook.ExclusiveAccess
If Err.Description = "Application-defined or object-defined error" Then
Exit Sub
End If
Else
Exit Sub
End If
End If
c = ActiveWorkbook.Styles.Count
Application.ScreenUpdating = False
For i = c To 1 Step -1
If i Mod 600 = 0 Then DoEvents
Set s = ActiveWorkbook.Styles(i)
Application.StatusBar = "Deleting " & c - i + 1 & " of " & c & " " & s.Name
If Not s.BuiltIn Then
s.Delete
If Err.Description = "You cannot use this command on a protected sheet. To use this command, you must first unprotect the sheet (Review tab, Changes group, Unprotect Sheet button). You may be prompted for a password." Then
MsgBox Err.Description & vbCr & "You have to unprotect all of the sheets in the workbook to remove styles.", vbExclamation, "Remove Styles AddIn"
Exit For
End If
End If
Next
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

Hope this helps.

wow will try it out thanks very much