Clearing Interior Colours or all sheets

[ExcelMonkey]

I am using this code to clear the interior colour of all
the worksheets in my workbook. When I run it, I get an
run time error 1004 "Unable to set Color Index Property of
Inerior Class. Why is this happening?


Answer = MsgBox(Prompt:="You have decided to print a
colour map. Accordingly, this routine will change all
your current formatting in the model to white in order to
colour it effectively. Please note that this will also
take out the existing conditionl formatting in the
spreadsheet.", Buttons:=vbYesNo + vbQuestion)
If Answer = vbYes Then
'Change colour index to white
'Also take out conditional formatting
For Each sh In ActiveWorkbook.Worksheets
sh.Cells.Interior.ColorIndex = xlNone
sh.Cells.FormatConditions.Delete
Next sh

End If

 

[Tom Ogilvy]

If your running this from a commandbutton in xl97, then set the takefocus on
click property of the command button to false.

Otherwise, my suspicion would be you have merged cells on one of your
sheets. However, in xl2003 it still ran with merged cells - so this is only
a guess.

If that isn't it, figure out which sheet is causing the error and see what
is unique about the sheet.

 

[JulieD]

Hi

it works fine for me ... XL 2003 ... do you have any protected sheets in
your workbook?

Cheers
JulieD

 

[ExcelMonkey]

Yep protected sheets were the culprit! Thanks

-----Original Message-----
Hi

it works fine for me ... XL 2003 ... do you have any protected sheets in
your workbook?

Cheers
JulieD




.

 

[JulieD]

you're welcome and thanks for the feedback

Yep protected sheets were the culprit! Thanks