Cell.interior.colorindex - does not work in Excel 97

[Ian]

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,

 

[Ron de Bruin]

Hi Ian

0 not exist
White is colorindex -4142

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

 

[Ian]

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 de Bruin]

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

 

[Peter T]

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

 

[Ian]

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,

 

[Ron de Bruin]

HIi Ian

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

 

[Ron de Bruin]

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 ?

 

[Peter T]

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

 

[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 ?

Thanks, Ron. That has sorted it !

It works perfect now.

Many thanks.

Ian,

 

[Ron de Bruin]

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.