Protect Cell from User Edit Based on Cell Interior Color

[MICowboy13]

Hi all,

I am in need of a code that allows me to protect cells based on the interior cell color. I have looked around and found a few things but I can't seem to get it to work. I think the main issue is that a few of the cells in the worksheets are merged.

What I need the code to do;
Protect each cell from user edit if the cell interior color is ColorIndex 15. I need it to do this for a worksheet array that consists of sheet names from 'Mach-1', 'Mach-2', 'Mach-3', all the way up to 'Mach-16'. Like I mentioned before, some of the cells are merged.

Is there a way to do this or am I out of luck?


Thank You!

[Arkadi]

merged cells can't be locked on their own, but they also have a mergearea property. I believe you can lock a cell's mergearea even if it is not merged so I think this will work, but is untested:

Please Login or Register  to view this content.

EDIT: As you probably know the sheet needs to be protected before the locked cell actually gets protected from change. If you want the code to lock the sheet also then you need to add a line between "Next myCell" and "Next sht_ctr", something like ws.Protect Password:="yourpassword"

[MICowboy13]

This works great. Thanks for your work!