Solved: Auto Protection

[dek]

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]

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]

Also, is the cell colored because you colored it, or due to conditional formatting?

[softman]

Dear Deg,

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

Kind regards

[lucas]

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]

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]

Option Explicit
Sub unProtectAllSheets()
Dim wk As Worksheet
For Each wk In ActiveWorkbook.Worksheets
wk.Unprotect
Next
End Sub

[dek]

Magic and thank you!