rltomalin
asked on
Conditional format depending on protection
This is not a must have, but I would like to be able to set the format of a cell dependant on whether ther Worksheet has protection turned on.
I had hoped to be able to do this via Conditional Formatting - but cannot fugure out a syntax for the formula to use.
Any suggestions gratefully received.
Regards
Richard Tomalin
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The outcome of the formula can be used as an input to determine conditional formatting, but there is one drawback : activating or de-activating sheet protection does not fire an event that can be handled within VBA as far as i know. So you would have to either manually refresh the formula in order for the correct value to show, or use / abuse another event macro to do this for you (eg. events such as sheet selected / cell selected / sheet changed or make a timed refresh macro which runs every second or so)
in that case, it might be far more easy to implement a macro which runs each time the sheet is activated :
pay attention though that the particular cell B2 must not be blocked by protection, because otherwise the blue color can not be set !
or use something like
Private Sub Worksheet_Activate()
If ProtectScenarios Then
Range("B2").Interior.Color = vbBlue
Else
Range("B2").Interior.Color = vbYellow
End If
End Sub
pay attention though that the particular cell B2 must not be blocked by protection, because otherwise the blue color can not be set !
or use something like
Private Sub Worksheet_Activate()
If ProtectScenarios Then
Unprotect
Range("B2").Interior.Color = vbBlue
Protect
Else
Range("B2").Interior.Color = vbYellow
End If
End Sub
ASKER
I gave balatheexpert 100 points because it did specifically answer the question. However akoster did give a detailed explanation of methods that could be used to accomplish the result asked for.
Regards
Richard
Regards
Richard