ColorIndex property XL2000 vs 2003

[MarkT]

I have recently upgraded from Office 2000 to 2003 Pro. I have a spreadsheet
containing VBA code that halts when run in 2003 with the following
'unexpected error':

Unable to set the ColorIndex property of the Font class

Code execution then stops. Here is the offending code line

Range(cell_dest).Font.ColorIndex = 0

I have tried changing the value to no effect and cannot find anything about
this error anywhere in the KB or newsgroups. Using VBA 6.3 in both 2000 and
2003 setups.

Any ideas anyone - this spreadsheet is critical to my work!

Tks
Mark

 

[Harald Staff]

Hi Mark

This runs fine in my 2003:

Sub Macro1()
Dim cell_dest As String
cell_dest = "A2"
Range(cell_dest).Font.ColorIndex = 0
End Sub

Is the sheet protected in any way ? Or is there a missing reference (in the
VB editor menu Tools > References) ?

Best wishes Harald

 

[Bob Phillips]

Mark,

There is no colorindex of 0, so I guess 2003 has just tightened up.

Try

Range(cell_dest).Font.ColorIndex = xlColorindexAutomatic

if using the Interior property, use xlColorindexNone.


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Toppers]

Mark,
Like Harald, you code works OK for me (XL 2003). ColorIndex=0 is
valid!

 

[Harald Staff]

Hi Bob

0 is "automatic", used by fonts, opposed to "none" for interior, which
is -4142. Recording those actions said the same in 2000 and 2003:

Sub Macro2()
Range("A2").Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0
End Sub

Best wishes Harald

 

[Ron de Bruin]

Hi Harald

0 is "automatic", used by fonts

for fonts it is -4105
MsgBox ActiveCell.Font.ColorIndex

But you can use 0 to set it
ActiveCell.Font.ColorIndex = 0

Is working for me also in 2003
There must be a other problem in the code from the OP

 

[MarkT]

Harald,

Oooohhhh maaaannnn!!!!! All that messing about with the code and it's all
down to the 'format cells' check box in the new protection in XL 03.
Simple...when yu damn well know!!

Tks a lot Harald - you've improved my weekend a lot .

 

[Bob Phillips]

Hi Harald,

I have no idea on 2003 as I don't have it, but automatic is -4105 on XL200
in front of me.

Bob

 

[Harald Staff]

I have no idea on 2003 as I don't have it, but automatic is -4105

Also when you record a macro while setting font color to automatic ?

on XL200 in front of me.

You /really/ need an upgrade my friend <bg>

Best wishes Harald

 

[Harald Staff]

Oooohhhh maaaannnn!!!!! All that messing about with the code and it's all
down to the 'format cells' check box in the new protection in XL 03.
Simple...when yu damn well know!!

Tks a lot Harald - you've improved my weekend a lot .

Glad to hear that Mark. Thanks for the feedback.

Best wishes Harald

 

[Bob Phillips]

Also when you record a macro while setting font color to automatic ?

Well I'll be bu*!*#ed. It was 0. At times MS even frustrates me!

You /really/ need an upgrade my friend <bg>

This is the pre-MS, wind-up version. Don't go in for this new-fangled stuff
<ebg>