excel - error 1004

question

I have a workbook in which I am using a macro to copy ranges from two worksheets into a third worksheet and then formatting it. The macro works fine for the copying but when it comes to the formatting I get 'Error 1004 unable to set Color property of the Interior class'. The formatting is applied range by range and the error always occurs at this line of code:

rng.Interior.Color = RGB(255, 255, 255)

Why is this code not working?

answer

Your code is tripping the error not because of the code itself but because your workbook has too many cell formats.

You are using Microsoft Excel 2003. This version has an upper limit of 4,000 formats per workbook. Now that sounds a lot until you consider how many you are using in your workbook.

These are some of the settings that are contributing to formats in your workbook:

• Typeface
• Font colour
• Font size
• Font style - bold, italic, underline
• Borders - left, right, top, bottom
• Border thickness and pattern
• Fill colour
• Fill pattern
• Number formatting
• Cell alignment.

Scanning your workbook (all seven worksheets) this is our rough count of formats in use:

• Typeface = 1
• Font colour = 3
• Font size = 4
• Bold = 2 (on or off)
• Italic = 2
• Underline = 2
• Border left thin = 2
• Border left thick = 2
• Border right thin = 2
• Border right thick = 2
• Border top thin = 2
• Border top thick = 2
• Border bottom thin = 2
• Border bottom thick = 2
• Fill colour = 5
• Fill pattern = 2
• Number formatting = 4
• Cell alignment = 3.

Now, we are not suggesting that all combinations of the above are in use but if we consider for arguments sake that they are, then that makes for nearly three million possible combinations.

It is quite likely that the combinations actually in use in your work book are approaching Excel's 4,000 limit. So when your macro starts its formatting it pushes to that limit and fails.

A starting point in reducing the number of formats in use is to rationalise the way in which you apply borders. There could be big savings there.

Bear in mind, for example, the different ways in which you can apply a border to the left- and right-hand edges of a cell and you can appreciate how formats-in-use can multiply:

• Border to left + border to the right (two actions from the Borders button on the Formatting Toolbar)
• Border to left and right applied in one action
• Border to right of cell to left of target cell + border to left of cell to right of target cell.

disclaimer

DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.