Why unable to set cell's ColorIndex property?

[Guest]

Greetings,

I'm getting the following error and do not understand why, so I'd appreciate
any insight you might be able to provide.

Run-time error 1004: Unable to set the ColorIndex property of the Font
class.

This error occurs in a routine that looks like this (with some irrelevant
code removed for clarity):

Sub SetTablesValidStatus()

Set rngCell = ActiveWorkbook.Names("Model_RTable_StatusMsg").RefersToRange
rngCell.Value = ">>> Update Needed <<<"
rngCell.Font.ColorIndex = 3 ' <-- error occurs here

End Sub

This SetTablesValidStatus routine lives in the code space of a sheet called
"Power Model" and when operations on that sheet end up invoking that routine,
everything works fine (i.e. no error while setting the ColorIndex property).

The problem occurs when I end up in SetTableValidStatus through actions on
another sheet that cascade through to the Power Model sheet. (Both sheets are
in the same workbook.)

Why would I not be able to set the ColorIndex property in the latter case?
Even though the Power Model sheet is not the active sheet at that time, the
target cell still exists (i.e. the cell named Model_RTable_StatusMsg) and it
has a Font.ColorIndex property regardless of whether it's visible.

I'm working in Excel 2002 with SP3 installed.

Again, I'd appreciate any tips you can provide.

Thank you.

Wolf

 

[John]

I think you need the sheet name in the code if its not being called from the
active worksheet
E.g (untested)
setRngCell = worksheets("Test").range("Data")

HTH
J

 

[Guest]

Hi John,

Thank you for the reply, although I don't think that's my problem. The name
Model_RTable_StatusMsg is a global name (i.e. in the names collection of the
workbook), and its definition includes the sheet specifier. If I query the
address of Model_RTable_StatusMsg on a sheet other than the Power Model
sheet, I get 'Power Model'!$AF$6 as expected.

And I have other cases where I'm successfully referencing names mapped to
one worksheet from other worksheets.

Any other ideas?

Wolf