Hi, I am using code to change the colour of cells in a 2003 workbook. It works fine as long as the sheet is unprotected, but as soon as I add protection I get:
Run-time error '1004': Unable to set the ColorIndex property of the interior class.
Does anyone have any ideas how to get round this? The code I am using is:
Please Login or Register to view this content.
Thanks, Mrs T
Last edited by jeffreybrown; 07-31-2012 at 11:53 AM. Reason: Please use code tags
You need to run an unprotect before the code and at the end protect the sheet again.
Please Login or Register to view this content.
HTH
Regards, Jeff
Thank you very much..and sorry about the code tags
No problem.
You are very welcome...Glad it works for you and thanks for the feedback
I'm now getting a Runtime error '9': Subscript out of range.
Is it possible to attach the offending workbook?
If I upload a copy of the page I'm working in will that help? The workbook is 17.9MB!
Yes, just the single worksheet would suffice.
I do not get the error when I run you code.
However: I had to disable events to run your code. You are changing value of a cell in the worksheet_change event! This will get you in infinite loop!
You need to add application.enableevents = false at the begining of you code and =true at the end
Last edited by vandan_tanna; 07-31-2012 at 01:46 PM.
Regards,
Vandan
colours.xls
It's putting the colour in now in this copy sheet, but I'm back to Run-time error 1004!
Thanks for your help,
Mrs T
I have to step away for a few minutes, but curious why you are using this code instead of the code I provided in the other thread?
Doesn't seem like you need to loop and why check for formulas when there aren't any (at least I can't see them).
try my suggestion in post #9...I think that will fix it
colours2.xls
Hi, Here's my other version - I tried it with both codes that were suggested and got myself into a pickle with both, so it's obviously something I'm doing. I have no idea why I'm looping or checking for formulas, other than the names are imported from another page in the full workbook.
@Vandan - thank you for trying to help, but I haven't got a clue what you mean!
This is what I meant
Please Login or Register to view this content.
Thanks Vandan, I pasted in your code, but again as soon as I password protect the sheet I get Run-time error 1004: Unable to set the ColorIndex property of the interior class. It works great without the password protection.
Are you sure you are unprotecting first like Jeffreybrown's post #2? I have a feeling you are protecting it in stead of unprotecting.
Following works for me...
Please Login or Register to view this content.
That's it! Thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks