0

I know this question came up several times already, but I believe that my problem is slightly different.

My goal is to color format every second line one an excel sheet for visual reasons. The code for that looks like this:

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lC = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
For i = 2 To lastRow
        On Error GoTo skipColor
        Set rng = ws.Range(ws.Cells(i, 1).Address, ws.Cells(i, lC).Address)
        rng.Interior.ThemeColor = xlThemeColorDark1
        If i Mod 2 = 0 Then
            rng.Interior.TintAndShade = -4.99893185216834E-02
        Else
            rng.Interior.TintAndShade = -0.249977111117893
        End If
skipColor:
        Debug.Print Err.Description
Next i

The iteration of the loop always stops at exact the same line. It seems as if Excel cache is full and no more cell formats can be saved. I tried the solution (second answer) from this post and used it for every iteration of the loop. No success.

One more thing: even though I added the On Error Goto skipColor line, it still shows me the error message.

EDIT: The line that is highlighted when I hit debug on the error message is: rng.Interior.ThemeColor = xlThemeColorDark1.

Community
  • 1
  • 1
Moritz Schmitz v. Hülst
  • 3,229
  • 4
  • 36
  • 63
  • How about using conditional formatting as in the accepted answer here: http://stackoverflow.com/questions/4629100/excel-vba-alternate-row-colors-in-range ? – LocEngineer Jul 31 '15 at 11:34
  • What index does it stop at? The first? The second? Next to last? What is the error message? – aphoria Jul 31 '15 at 11:56
  • @aphoria Index: 573 out of 742. Error message: "Run-time error '1004': Too many different cell formats.". – Moritz Schmitz v. Hülst Jul 31 '15 at 12:20
  • @LocEngineer, thanks but this produces a "Run-time error '5': Invalid procedure call or argument." in the second line of the `GreenBarMe` sub. I deleted the first btw (that gave me the same error as in my question). – Moritz Schmitz v. Hülst Jul 31 '15 at 12:22
  • @moritz-schmitz-v-hülst No, not the VBA solution but the conditional formatting from the **accepted** answer. – LocEngineer Jul 31 '15 at 12:36

2 Answers2

0

You are not trapping the error correctly. Because you have placed the label

skipColor:

inside the loop, you will hit the Debug.Print even though there is no error.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0

According to the Microsoft KB article You receive a "Too many different cell formats" error message in Excel, it appears you have exceeded the allowed number of different cell formats.

From the article:

Cause

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. A combination is defined as a unique set of formatting elements that are applied to a cell. A combination includes all font formatting (for example: typeface, font size, italic, bold, and underline), borders (for example: location, weight, and color), cell patterns, number formatting, alignment, and cell protection.

NOTE: If two or more cells share exactly the same formatting, they use one formatting combination. However, if there are any differences in formatting between the cells, each cell uses a different combination.

In Excel style counts may increase when copying between workbooks because custom styles are copied.

A workbook that has more than 4,000 styles may open in Excel 2007 and higher (because of the increased limitation for formatting) but result in an error in Excel 2003.

The Resolution section provides a link to a tool called XLStylesTool that may be able to help you cleanup you workbook.

aphoria
  • 19,796
  • 7
  • 64
  • 73