Excel Error 1004
Laste updated on 2018-07-31 by David Wallis.
“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?”
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:
- 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 my 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.
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:
- 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.
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.
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.