dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

Excel Error 1004

Laste updated on 2018-07-31 by David Wallis.


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?”


Response

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

You’re using Microsoft Excel 2003. This version has an upper limit of 4,000 formats per workbook. (In Excel 2013 and 2016 it’s 64,000.) 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:

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

I’m 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:


Give DMW’s Tips Your Support

ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.

Thanks, in anticipation.


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.

“Well, we all make mistakes, dear, so just put it behind you. We should regret our mistakes and learn from them, but never carry them forward into the future with us.”

Lucy Maud Montgomery, Anne of Avonlea (1909)