I have the following code in an excel 2000 spreadsheet:



Private Sub CommandButton1_Click()

For Each cell In Range("c5:H32")
cell.Interior.ColorIndex = 0
Next cell

End Sub




But it does not work in Excel 97.

Any ideas why? I get run time error 1004

Ian,

Hi Ian

0 not exist
White is colorindex -4142

Try this in 97
ActiveCell.Interior.ColorIndex = -4142


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ian" wrote in message ...
I have the following code in an excel 2000 spreadsheet:



Private Sub CommandButton1_Click()

For Each cell In Range("c5:H32")
cell.Interior.ColorIndex = 0
Next cell

End Sub




But it does not work in Excel 97.

Any ideas why? I get run time error 1004

Ian,

On Tue, 5 Apr 2005 17:23:08 +0200, "Ron de Bruin"
wrote:

Hi Ian

0 not exist
White is colorindex -4142

Try this in 97
ActiveCell.Interior.ColorIndex = -4142


Sorry, Ron,

Still does not work.

Cheers,

Ian,

Is it working if you run the macro with Alt-F8 instead of
using the button

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ian" wrote in message ...
On Tue, 5 Apr 2005 17:23:08 +0200, "Ron de Bruin"
wrote:

Hi Ian

0 not exist
White is colorindex -4142

Try this in 97
ActiveCell.Interior.ColorIndex = -4142


Sorry, Ron,

Still does not work.

Cheers,

Ian,

Ron & Ian.

xlAutomatic = -4105
xlNone = -4142

Font cannot be formatted with xlnone, colorindex 1-56 or xlAutomatic only.
But "No Fill" for interior is xlNone.

Regards,
Peter T


"Ron de Bruin" wrote in message
...
Is it working if you run the macro with Alt-F8 instead of
using the button

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ian" wrote in message
...
On Tue, 5 Apr 2005 17:23:08 +0200, "Ron de Bruin"
wrote:

Hi Ian

0 not exist
White is colorindex -4142

Try this in 97
ActiveCell.Interior.ColorIndex = -4142


Sorry, Ron,

Still does not work.

Cheers,

Ian,

On Tue, 5 Apr 2005 17:34:14 +0200, "Ron de Bruin"
wrote:

Is it working if you run the macro with Alt-F8 instead of
using the button


I hve put the code in a macro and run it with AltF8
and it does not work, does it does not show up n error.

Ian,

HIi Ian

I will setup a virtual pc with Excel 97 now to test it.




--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ian" wrote in message ...
On Tue, 5 Apr 2005 17:34:14 +0200, "Ron de Bruin"
wrote:

Is it working if you run the macro with Alt-F8 instead of
using the button


I hve put the code in a macro and run it with AltF8
and it does not work, does it does not show up n error.

Ian,

If you change the takefocusonclick property of the button to false
you don't see the 1004 error (bug in 97)

The code is working for me in Excel 97 as a sub and in the click event of the button

But if your colors are a result of Format conditional formatting it is not working.
Is that true ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ian" wrote in message ...
On Tue, 5 Apr 2005 17:34:14 +0200, "Ron de Bruin"
wrote:

Is it working if you run the macro with Alt-F8 instead of
using the button


I hve put the code in a macro and run it with AltF8
and it does not work, does it does not show up n error.

Ian,

Sorry folks - somehow I read you were changing font.
Must be going dyslexic!


xlAutomatic = -4105
xlNone = -4142

Font cannot be formatted with xlnone, colorindex 1-56 or xlAutomatic only.
But "No Fill" for interior is xlNone.

Regards,
Peter T

On Tue, 5 Apr 2005 19:01:43 +0200, "Ron de Bruin"
wrote:


If you change the takefocusonclick property of the button to false
you don't see the 1004 error (bug in 97)

The code is working for me in Excel 97 as a sub and in the click event of the button

But if your colors are a result of Format conditional formatting it is not working.
Is that true ?

Thanks, Ron. That has sorted it !

It works perfect now.

Many thanks.

Ian,

You are welcome

You confused me with saying that the sub not work

I hve put the code in a macro and run it with AltF8
and it does not work, does it does not show up n error.


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ian" wrote in message ...
On Tue, 5 Apr 2005 19:01:43 +0200, "Ron de Bruin"
wrote:


If you change the takefocusonclick property of the button to false
you don't see the 1004 error (bug in 97)

The code is working for me in Excel 97 as a sub and in the click event of the button

But if your colors are a result of Format conditional formatting it is not working.
Is that true ?

Thanks, Ron. That has sorted it !

It works perfect now.

Many thanks.

Ian,