"Too many different cell formats" Error After Copying and Pasting Between Spreadsheets
Recently I was working in an Excel spreadsheet and had to deal with an unusual problem. While trying to make some simple formatting changes I got an error message reading, "Too many different cell formats", and the changes did not take effect.
This is a strange problem to have because Excel 2007 and higher can accommodate up to 64,000 different cell styles. Multiple cell styles may be replicated when copying and pasting data from one spreadsheet to another. Identical styles at a 20% accent are created.
A quick and easy solution to the problem is the macro posted here.
Dim N As Long, i As Long
N = .Styles.Count
For i = N To 1 Step -1
If Not .Styles(i).BuiltIn Then .Styles(i).Delete
Running this macro will automatically remove all of the cell styles, and the error will no longer appear when you make formatting changes.