Clearing Interior Colours or all sheets
 

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

Clearing Interior Colours or all sheets
 

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.

--
Regards,
Tom Ogilvy

"ExcelMonkey" wrote in message
...
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

Clearing Interior Colours or all sheets
 

Hi

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

Cheers
JulieD

"ExcelMonkey" wrote in message
...
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

Clearing Interior Colours or all sheets
 

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

"ExcelMonkey" wrote
in message
...
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


.

Clearing Interior Colours or all sheets
 

you're welcome and thanks for the feedback


"ExcelMonkey" wrote in message
...
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

"ExcelMonkey" wrote
in message
...
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


.