Macros Won't Work on Protected & Hidden Cells

    Hi there,


    I have to say I'm not good at this, I have manged to get a code that changes the colour of the cells according to what's in them! Easy stuff no doubt but took me ages!
    The problem is that a lot of the cells need to be hidden and or protected as many people use this and I don't want certain cells to be changed, but when I protect these cells, the code doesn't work.


    I have read a number of posts which says to do the following


    Add the line:
    Activesheet.Unprotect "password"

    at the beginning of the code to unprotect the sheet (change the password as desired) and then add the line:

    ActiveSheet.Protect "password"

    At the end of the code to reprotect it when it is done.


    But when I do this I get an error (runtime 1004 I think) and I feel like I have tried everything! I know this is probably a really easy one for you, but I'm lost!

    I know that similar q's have been asked, but I can't seem to make that code work without an error!


    Hoping someone canhelp


    Sam

    Re: Macros Won't Work on Protected & Hidden Cells


    If you did not use a password to protect the sheet initially, leave that off and just use the Unprotect/Protect. Works great all the time.


    If all you are doing is controlling the cell format based on values in the cells, you may want to look at Conditional Formatting instead. Much easier to do and manage compared to VBA.

    Re: Macros Won't Work on Protected & Hidden Cells


    Hi ShosMeister,

    thanks for the reply! Conditional Format is useless, I have around 20 conditions, excel only allows 3 hence the need for the code. If I lock the document, the macros don't work. Any help would be great!


    Sam

    Re: Macros Won't Work on Protected & Hidden Cells


    Office/Excel 2007 and up expanded the conditions so you are no longer limited to 3 but understand the desire to have it in VBA for easier management/adjustments.


    As was asked, did you actually use a password to protect the sheet? Did you actually protect the sheet or the entire workbook? This all makes a difference.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login