Discussion:
|
When developing Excel spreadsheets for other users, it is a best practice to shade all cells that you want your users to enter data into. This allows your users to know exactly where they should (and shouldn't) enter data. This procedure allows you to quickly ensure that all shaded cells are unlocked, and all unshaded cells are locked on each sheet in the workbook. Once sheet protection is applied to your worksheets, users will only be able to enter data in the shaded cells. This macro is coded to unprotect only light green cells, but the sample file also includes a utility to evaluate any cell color so that you can replace it with the color (colour) you prefer. PLEASE NOTE: The intention of this entry was to be used by the spreadsheet developer, and not the end user. Because of this, it was assumed that the developer would know that all of their sheets were uprotected, run this macro, then protect the appropriate sheets. If you choose to use this in a user's setting, the author would highly recommend testing all sheets for sheet protection prior to running the macro as no error handling exists in this routine. A KB entry to do this can be found at http://www.vbaexpress.com/kb/getarticle.php?kb_id=551.
|
Option Explicit
Sub UnprotectGreenCells()
Dim cl As Range, ws As Worksheet, lColor As Long
lColor = 35
For Each ws In ActiveWorkbook.Worksheets
For Each cl In ws.UsedRange
If cl.Interior.ColorIndex = lColor Then
cl.Locked = False
Else
cl.Locked = True
End If
Next cl
Next ws
End Sub
|