Today, the questioner asks how to unprotect the green cells. There isnt any good way to do this in the Excel user interface, so in Episode 593, I use a tiny bit of VBA code to achieve the effect.
The code used in the podcast is:
Sub UnProtectGreen
For each Cell in Selection
If Cell.Interior.ColorIndex = 4 then
Cell.Locked = False
Else
Cell.Locked = True
End If
Next Cell
End Sub
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
The code used in the podcast is:
Sub UnProtectGreen
For each Cell in Selection
If Cell.Interior.ColorIndex = 4 then
Cell.Locked = False
Else
Cell.Locked = True
End If
Next Cell
End Sub
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we have a question that came in one of the power Excel seminars and every once in a while you get a question that just kind of makes your head spin the guy said hey, I wanna be able to protect the entire worksheet except for the green cells.
Wow, that's a tough one. There's no good way to go through and protect everything except for the green cells and this is one where I think the best solution is to go through and use a little bit of VBA.
Here's what I'm gonna do.
I'm gonna select one of the green cells and we'll switch over to VBA with Alt+F11.
I wanna do something quick in the Immediate window, The Immediate windows control+G. I must say, print activecell.interior.colorindex.
That'll tell me that that particular green has a color index of 4, Now, we can go back up and insert a quick module here.
Insert, Module.
Now, create a new macro I'll call it Sub UnprotectGreen.
Well, say For Each cell in Selection if cell.interior.colorindex=4, then cell.locked =False Else cell.locked = True End If, and Next cell.
Now we have this tiny little macro here that will go through and basically look at all the cells in our selection if the cell is green, it's going to unlock it otherwise it will leave it as lock.
So, we'll select basically the whole area of the spreadsheet Alt+F8, will give me a list of the Macros run UnprotectGreen and we've now gone through and basically unlocked the green cells.
Final step Tools, Protection, Protect Sheet and you'll see that if I try and change one of the green cells, it lets me change it.
If I try and change any other cell, it's a protected cell.
So, now I'm sure the person who asked this question had a much more convoluted spreadsheet lots of green cells.
I guess if you had three, you could just go through and unlock them yourselves.
But certainly if you had a large spreadsheet, and for some reason you need to unlock all the green cells just the input cells using a little bit of Macro code is the way to go.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question that came in one of the power Excel seminars and every once in a while you get a question that just kind of makes your head spin the guy said hey, I wanna be able to protect the entire worksheet except for the green cells.
Wow, that's a tough one. There's no good way to go through and protect everything except for the green cells and this is one where I think the best solution is to go through and use a little bit of VBA.
Here's what I'm gonna do.
I'm gonna select one of the green cells and we'll switch over to VBA with Alt+F11.
I wanna do something quick in the Immediate window, The Immediate windows control+G. I must say, print activecell.interior.colorindex.
That'll tell me that that particular green has a color index of 4, Now, we can go back up and insert a quick module here.
Insert, Module.
Now, create a new macro I'll call it Sub UnprotectGreen.
Well, say For Each cell in Selection if cell.interior.colorindex=4, then cell.locked =False Else cell.locked = True End If, and Next cell.
Now we have this tiny little macro here that will go through and basically look at all the cells in our selection if the cell is green, it's going to unlock it otherwise it will leave it as lock.
So, we'll select basically the whole area of the spreadsheet Alt+F8, will give me a list of the Macros run UnprotectGreen and we've now gone through and basically unlocked the green cells.
Final step Tools, Protection, Protect Sheet and you'll see that if I try and change one of the green cells, it lets me change it.
If I try and change any other cell, it's a protected cell.
So, now I'm sure the person who asked this question had a much more convoluted spreadsheet lots of green cells.
I guess if you had three, you could just go through and unlock them yourselves.
But certainly if you had a large spreadsheet, and for some reason you need to unlock all the green cells just the input cells using a little bit of Macro code is the way to go.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
