Conditional formating based on sheet protection status

[chrisgarcia78]

Hi there,

Is there a way to automatically format a cell color based on the sheet protection status? if the sheet is unprotected to change cell A1 background color to RED and Green if protected.

Thank you.

 

[Fluff]

Not that I'm aware of.

 

[Joe4]

You may be able to use VBA, if you are open to that idea.

 

[chrisgarcia78]

You may be able to use VBA, if you are open to that idea.

Yes I'm open

 

[Joe4]

How many sheets are in your workbook?
Do you want to apply this to all sheets, or just certain ones?

 

[chrisgarcia78]

How many sheets are in your workbook?
Do you want to apply this to all sheets, or just certain ones?

I want to do it for each sheet individually, I have 10 sheets but I need to format Sheet1:A1 if sheet1 is unprotected, Sheet2:A1 if sheet2 is unprotected

 

[Joe4]

OK, this is a little trickier than I thought. At issue is the following:

1. It might not update right away as you protect/unprotect sheets. There are many different "event procedures" in VBA. These are "events" that happen that automatically fire VBA code to run. These include things like opening a workbook, selecting/activating a sheet, selecting/activating a cell, changing a cell value, a calculation is fired, etc. The issue is that I don't think changing the protection status of a sheet would automatically "fire" the VBA code to call. So while we could have the code fire automatically as you activate a sheet, if you are in the sheet and change the protection status, the color of cell A1 would not change until you leave that sheet and come back to it.

2. We may not be able to change the color of cell A1 if the sheet is protected (as protection would prevent that from happening). The way to get around that is to have the VBA code being fired temporarily remove the protection, change the color of cell A1, and then re-protect the sheet. That can all be done via VBA code, but if the sheet is protected with a password, that password would need to be included in the VBA code.

What are your thoughts on those 2 potential issues?

 

[chrisgarcia78]

I needed to be updated right away after unprotecting the sheet, actualy don't need to change the color of the cell when is protected, with just changing it when is not protected will be perfectly fine. Thank you for taking your time responding to this post.

 

[Joe4]

I needed to be updated right away after unprotecting the sheet, actualy don't need to change the color of the cell when is protected, with just changing it when is not protected will be perfectly fine. Thank you for taking your time responding to this post.

Because of what I mentioned in item #1 in my previous post, I don't know that this is possible, as there is no VBA event to capture "protection changes".
It could be added to Sheet or Cell selection event procedures (i.e. fire when a sheet or cell is selected AFTER the protection is changed), but that may be the best that can be done.

So what you want may not be possible, after all, if it has to happen immediately after the protection is changed, regardless if nothing else happens.

 

[chrisgarcia78]

Because of what I mentioned in item #1 in my previous post, I don't know that this is possible, as there is no VBA event to capture "protection changes".
It could be added to Sheet or Cell selection event procedures (i.e. fire when a sheet or cell is selected AFTER the protection is changed), but that may be the best that can be done.

So what you want may not be possible, after all, if it has to happen immediately after the protection is changed, regardless if nothing else happens.

Ok I understand, so if it works after updating any value of any cell in the sheet after unprotecting the sheet then I can take that.