How to Fix Excel VBA Error 1004 — Unable to Set Color Property

How to fix Excel VBA Error 1004, Unable to Set Color Property of the Interior Class, due to too many formats applied to an Excel workbook.

Laste updated on 2024-05-02.

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 onward, 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), I note it has numerous combinations of all of the above across cells that contribute to the composition of your spreadsheets. Additionally there appear to be cells that are not contributing yet have formats applied to them.

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

Making Way for Additional Formatting

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.

Getting rid of applied formats and starting again is one way of removing a surfeit of formats. Select the cells from which you want to remove the surfeit, from the Editing group on the Home tab click Clear:

Choose Clear Formats to return the format of the selected cells to the General format.

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.

Disclaimer

David Wallis 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.