Runtime '1004' Error

[Dave_2k5]

I have a command button on a worksheet, which has the following code:

-----------------
Private Sub action_completed_Click()

Selection.Font.ColorIndex = 3

End Sub
-----------------

What I want this to do is change the font colour of the cell or cells I have currently selected to red. When I click the button it returns a runtime '1004' error...

What I'm a doing wrong??

Hope you can help

Thanks

Dave

[DonnaG]

As you are trying to apply a text...Is The Run-Time Error quoting something along the lines of no new fonts can be applied to this workbook? and have you got a number of charts present in the workbook. Is so, try selecting the charts RHC - Format Plot Area - Font Tab and deflag the autoscale at the bottom. You will then need to format the text but should relieve the problem...prsuming this is your problem.

[MattShoreson]

Dim xlRng As Excel.Range
Set xlRng = Selection
xlRng .Font.ColorIndex = 3

[Dave_2k5]

The run time error is quoting "Unable to set the ColorIndex property of the Font Class"

There are no charts, I have even open a new excel file put the button on a blank page and tried to get the button to change the font colour of the selected cell to red, and it still doesn't work!!!!

HELP!!

[Dave_2k5]

Matt,

Thanks for the reply, I have tried that code but it still coming back with the runtime error, the run time error is quoting "Unable to set the ColorIndex property of the Font Class"

I have even tried to run this code in a new file with a new button etc and it still doesn't work...

Any other thoughts???

[MattShoreson]

it seems most weird.

what's in the selection? Is there any other code you have that is before the setting the font colour?

[Dave_2k5]

I know, I thought it would be simple to do!!!

There is no other code in the whole file!!!

It's a new file, with just a cell which I am selecting, which at the moment has "hello" written in it, and a button with the code assoicated with it.

Dave

[MattShoreson]

What happens when you try an exact ref?

e.g.ActiveSheet.Cells(1, 1).Font.ColorIndex = 3

[Dave_2k5]

Matt,

I have been working on this file on my work computer and they are running Excel 97!!

I have now just tried both the code you replied with and the original one I sent on my personal laptop running Excel 2003 and they both work!!!

Do you know why it doesn't work in 97??

Dave

[MattShoreson]

It might be a compatability issue between the versions of visual basic they running on.

what version have they got? your 2003 should e vb6.3

[Dave_2k5]

I can't find the version of VB in excel 97, when you go to help | about it just says "Microsoft Visual Basic, Copyright 1986 1996" really helpfull!!!!

I have also tried the exact ref code and that also has the same error. The only why it works is if I use this code:

-----------------------
Private Sub CommandButton1_Click()

Range("A1").Select
Range("A1").Font.ColorIndex = 3

End Sub
-----------------------

But I want it to able to apply the color change on the selected cell(s) and not a predefined cell(s)