PDA

View Full Version : Solved: Auto Protection



dek
04-26-2010, 07:09 PM
Hi,

I would like a macro that protects all the cells in every sheet of the workbook except for a coloured cell (an input cell).

With no VBA knowledge, any assistance would be greatly appreciated.

Regards,
dek

Aussiebear
04-26-2010, 11:14 PM
Will you be changing the format of the sheet at any time or is it a predefined cell or range of cells that are coloured prior to use?

GTO
04-26-2010, 11:44 PM
Also, is the cell colored because you colored it, or due to conditional formatting?

softman
04-28-2010, 01:01 AM
Dear Deg,

Please find attached a sample with the solution.
Hope this help.

Kind regards

lucas
04-28-2010, 08:25 AM
Since you don't seem to be willing to respond to questions maybe this will work. We can't help you if you won't help us.

Option Explicit
Sub ProtectAllSheets()
Dim wk As Worksheet
Dim Rcell As Range
For Each wk In ActiveWorkbook.Worksheets
For Each Rcell In wk.UsedRange
If Rcell.Interior.ColorIndex = 6 Then
Rcell.Locked = False
End If
wk.Protect , userinterfaceonly:=True
Next
Next
End Sub

dek
04-28-2010, 04:20 PM
Lucas,

Thank you very much for the solution and apologies for the delayed reply.

Can I request one addition. The inclusion of a macro to unprotect all worksheets so that any development can be undertaken?

I know that I could unprotect each sheet manually, but a VBA outcome is more efficient.

Thanks

lucas
04-28-2010, 08:10 PM
Option Explicit
Sub unProtectAllSheets()
Dim wk As Worksheet
For Each wk In ActiveWorkbook.Worksheets
wk.Unprotect
Next
End Sub

dek
04-29-2010, 10:16 PM
Magic and thank you!