EXCEL VB Macro Run Time Error 1004 - Setting ColorIndex

[Erwin]

A macro developed and running ok under Offixe/XP prodcues
Run time error 1004 "Unable to set Colour Index for
Interior Class" when the spreadsheet macro is run on a PC
running XP but EXCEL prior to Office XP. I have beleive
the problem VB statement are:

Set Rng = ActiveSheet.Range("B6:U55")
Rng.Interior.ColorIndex = xlNone

A work around would be appreciated.

 

[mudraker]

Code works ok on my excel 9

 

[Dave Peterson]

When you say that other pc is running XP, is that winXP?

I'm guessing yes. If the version of excel prior to xl2002 is xl97, then it
could be a bug in xl97.

If you run a macro from a control on a worksheet from the controltoolbox
toolbar, you can either toggle the .takefocusonclick to false for that control
or add:

activecell.activate

to the top of your code.

If that prior version wasn't xl97, then ignore this message.

 

[Tom Ogilvy]

Is the sheet protected. No reason it shouldn't work on an unprotected
sheet.

 

[ross]

If the colour index is changed somewhere in the code , i.e it value is
cahnges, bit maybe that the value is too large, or small, excel, 97,
has a range for charts of 0-60, i think!
good luck
ross