Having a cell display certain text or formatting when the worksheet is protected or unprotected

[jonco]

Is there an easy way to have a cell display certain text or formatting when
the sheet is protected or unprotected? I'd like to have several cells,
A3232 be colored red (for instance) when the worksheet is unprotected.

You guys have been a GREAT help to me during this project. Thanks!!!!!

Jon

 

[Jim Cone]

This handles the entire used range on the active sheet.
Change the range to suit...
'------------------------------
Sub ColorWhatsLocked()
Dim rngCell As Excel.Range
Application.ScreenUpdating = False
ActiveSheet.UsedRange.Interior.ColorIndex = xlColorIndexNone
For Each rngCell In ActiveSheet.UsedRange.Cells
If rngCell.Locked Then rngCell.Interior.ColorIndex = 3
Next 'rngCell
Application.ScreenUpdating = True
End Sub

Sub RemoveColor()
ActiveSheet.UsedRange.Interior.ColorIndex = xlColorIndexNone
End Sub
-------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"jonco" <[email protected]>
wrote in message
Is there an easy way to have a cell display certain text or formatting when
the sheet is protected or unprotected? I'd like to have several cells,
A3232 be colored red (for instance) when the worksheet is unprotected.
You guys have been a GREAT help to me during this project. Thanks!!!!!
Jon

 

[Stefi]

This is a semi-automatic solution:
Install this UDF:
Function isprotected()
Application.Volatile
isprotected = ActiveSheet.ProtectContents
End Function

Apply = isprotected() as formula in Conditional formatting!

It comes to force automaticly when opening the workbook, but unfortunately
you have to press F9 (recalculate) to see the effect when changing Protect
Status!

Regards,
Stefi


„jonco†ezt írta:

 

[Ken Johnson]

Hi jonco,

Protecting the worksheet is not an event that can trigger any code,
however, you could try the following code in either a
Worksheet_SelectionChange Procedure or a Worksheet_Calculate
Procedure...

On Error GoTo PROTECTED
Range("A3232").Interior.ColorIndex = xlNone
Exit Sub
PROTECTED:
Me.Unprotect "password"
Range("A3232").Interior.ColorIndex = 3
Me.Protect "password"

Edit "password" to your protection password or remove both if you don't
use one.

I believe that if any cell on the sheet has the RAND function it will
force the sheet to calculate everytime you press Enter. This would be
handy if the code is in the Worksheet_Calulate Procedure.

Ken Johnson

 

[Ken Johnson]

Hi Jonco,

I take back what I said about Rand function forcing sheet calculation,
so it's probably best to use the SelectionChange event.

Ken Johnson