Imported VBA code not working....

Hello all...

Last year, member rylo, helped me with some VBA code for one of my projects. If you are reading this post, rylo, you might recall the VBA code that you helped me with...and maybe you can help me once more. Otherwise, maybe some other members can help me.

The VBA code below was provided by rylo last year. The code was designed to cycle through a list of teams...and FILL the background of each cell in the list with color...depending on which named range a match was found in.

Please Login or Register  to view this content.

This year I put together a slightly different spreadsheet and made the following changes to the VBA to conform to my new spreadsheet:

Please Login or Register  to view this content.

When I run the code, I get a Runtime error -' 1004': Method 'range' of object '_global' failed. (This error occurs on the VBA code line that is enlarged in the second version of the code, above. I tried to change the font color...but my color palette wouldn't open).

Three questions....

1. What part of the code did I change incorrectly? ...Or... Did I overlook any needed change(s)?

2. How can I format the font color along with the background color using this VBA?

3. The range $E$5:$AR$36 on sheet "MasterCopy" has been given a name.
Can't I replace the RANGE ($E$5:$AR$36) with the RANGE NAME ("Selections_MasterCopy")?

Thank you for any help you can provide....

The original code has two columns (E54:F112), the new version only one (F8:F73), might that be an issue?

The referenced columns contain dates.

In the older version, the dates appeared in merged cells.
The dates in the new version occupy individual cells.

Husker

Vaguely remember this. Can you attach an example workbook of your current structure with the new code.

rylo

Can you attach an example workbook of your current structure with the new code.

You bet I can.....!!!

But...don't have the time at the moment. (dammit...!!!)

Will get it to you as soon as I can. (Might be tomorrow....don't give up on me....)

Here is the truncated spreadsheet:

Hi

Took me a while!!!

Change the line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

You will also have to get rid of the days in sheet2!G19:G27 and make them teams.

When you have done that, if you can't get it to work, then put up the revised workbook and I'll have another look.

rylo

Thanks, rylo....

I made the changes to the VBA code as suggested.

As far as the DAYS were concerned, I simply eliminated them, as I don't
really need them. Then I moved all of the teams one column to the left.

When I press the RANDOMIZE button on Sheet2...I get a Run-time error.

Run-time error '1004':
Unable to set the ColorIndex property of the Font Class


This error occurs AFTER your code changes...near the end of the subroutine;

Please Login or Register  to view this content.

Also, is there a simple way to add code to set the FONT for each CASE above??

I've attached the updated sample for your inspection.
Thanks, so much, for your help...

Hi

Instead of actioning a range that has teams, you are going from column 5 to column 43 and this range included blank columns (21-23). It is having trouble establishing a value for colorind in the case statement.

Perhaps enhance the case statement to have a default colorind of say xlnone. See how that goes.


rylo

I'm going to try that......

Or....Maybe is should just execute the procedure TWICE....once for each half of the range...........????????

Hi

That would probably be more certain.

rylo