Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Cell Color Counts - Part 4

0

Hi all and Variatus,

I have tried to start a clean spreadsheet and get 24 boards on it. For the first 4 it kinda work with running Variatus' code - thank you, but for anything further and now...I get the error below.

Run-time error '1004' Unable to set the NumberFormat property of the Range class

Debug point to the line below, but I have no idea what to change or update there?

StartAt.Offset(R - 1, C - 1).NumberFormat = Chr(34) & Join(Fmt, "-") & Chr(34)

Even if I try to assign a value to the remaining cells the old fashion way, Excel give the following warning/error:

No more cutom number formats can be added

Help please...again - Spreadsheet attached.

Woutie

Answer
Discuss

Answers

0
Selected Answer

I was able to reproduce the error using your workbook. Research shows, however, that there shouldn't be a problem. There are 64,000 different formats available in an Excel 20007 (and later) workbook. I suggest you try again with a totally new one.

To better understand the problem please refer to this link. They offer a tool for cleaning up an older workbook as well.

Anyway, if you can't get the solution to work I suggest you try to make friends with the other one. It's not as delightful as this one but it should be able to do the job.

Edit 04 Aug 2018   =================================

Sorry to reply to your discussion here. I can't the Add to Discussion button to respond. I have been imagining a work-around but its a major job - a few days of research and coding. I think the benefit isn't worth the investment. The other solution I already posted just requires you to understand that update takes place when you leave the cell, not when you modify it.

The other reason why I don't want to invest the time is that your workbook was designed to ask a question. A working solution must be applied to a workbook which was designed to do the job. The worksheet/workbook design must be settled and final before work might commence. This means that you should fine-tune your worksheet and work flow with the VBA solution you now have and only then look for improved code. My guess is that by the time you have done the former you may not feel the need for the latter lol:

Discuss

Discussion

Hi Variatus,
I just thought I would update all on my research to my issue. It seems there is nothing wrong with your code, its a limitation with Excel itself. Excel can not take more "Custom Formats" that is why your VBA code does not work when I get to the 4th or 5th panel board. Since each board has 48 individual cells with each a custom format. Bloody Excel! Thank you for your help in any case, I do not think there is a work-around this problem...

Woutie
woutie (rep: 10) Aug 3, '18 at 9:05 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login