tel 01732 833085 · e-mail 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 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:
Scanning your workbook (all seven worksheets) this is our rough count of formats in use:
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:
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.