Change cell fill color when unprotected cell selected -

I'm struggling to find some code that changes the color (to light grey) of an unprotected cell in my sheet when its selected. When an unprotected cell is selected I want it to change color. The cell is then filled with a value and its color to return to original color when next unprotected cell selected.

I don't know if this code meets your requirements, but maybe you find it a bit helpful:

Please Login or Register  to view this content.

Point for you barryleajo - I didn't think that there may be some protected cells already filled with color (but I am still a beginner in VBA and Excel).
Anyway here is my version of code that preserves colors in protected cells:

Please Login or Register  to view this content.

to be added above all procedures in Sheet module (global variable)

Please Login or Register  to view this content.

I also added that line:

Please Login or Register  to view this content.

to avoid saving Workbook with unprotected cell filled with color

I don't know if the code is written properly, but it works for me

Hi Sbarro79. Your first code worked great but I lost the fill colour in all my protected cells. Could you please explain where I put 'Private prevcell As Range' within the code you have given. Much appreciated.

Hi Stratfordoaks, sorry for late reply - work
For the code line you asked: just open your workbook, open VBA editor (ALT+F11), in the top left panel select your worksheet (double click) and the code should be put in the first or second line (if the first line is Option Explicit)

I also changed the code:

Please Login or Register  to view this content.

to that code:

Please Login or Register  to view this content.

because I experienced that previous code didn't work when I clicked save button before closing workbook. And now, I hope, it will be alright.
Please remember to change "x" to worksheet name you work on.

I hope I explained everything clearly enough.

Hi Sbarro79 - I did get it working yesterday but I have other macros that run from this sheet before it saves and closes. I think the code was protecting cells and stopped these macros working. Its very frustrating because the grey fill colur in selected cell made it far easier for the operator to see which cell they were in. I need your code to not work when my oter ones run. I dont know if thats possible? Ps could you put all your code together for me so that I am sure I have it all in order. Im not quite sure which bits you want me to use. Many thanks for your time and patience.

Sorry to hear that there is a problem with my code. I'm just guessing that your worksheet was already protected when you open the workbook and this didn't allow your macros to be executed. But as I wrote, I'm still a beginner in VBA and I need to learn more about it, and learn, and learn...
In the attached file you will find code (slightly updated, if we can say like that) plus some silly simple macro (commandbutton_click and trial procedure) that works when sheet is protected (you don't need to add it to your worksheet). Please check, if the code works now as you wanted.

Thanks Sbarro79. I will have a dabble in the morning and let you know how it goes.