Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing cells without clearing formulas | Excel Discussion (Misc queries) | |||
colours in spread sheets | Excel Discussion (Misc queries) | |||
Excel 2003 font colours and cell colours | Excel Discussion (Misc queries) | |||
Select All Sheets and clear Interior Cell colours | Excel Programming | |||
Code to change interior colour only if current interior colour is | Excel Programming |