lock cells with specific layout

[hansjuh]

Currently I am working on a large worksheet. I am working on the fina
stage in which i want to protect it.
My sheet is completely colorcoded. There are about 1000 cells with
white background which the user may edit. All other cells (also man
more than 1000) must be locked.

I now have locked all cells. But I am not very fond of unlocking over
1000 cells by hand. Is there a piece of code to automate this?

something like:

if cell background = white
than cell = unlocked
else cell = locke

 

[Dave Peterson]

Does white mean none?

Option Explicit
Sub testme01()

Dim myCell As Range
With Worksheets("sheet1")
.Cells.Locked = True 'lock all of them
For Each myCell In .UsedRange.Cells
If myCell.Interior.ColorIndex = xlNone Then
myCell.Locked = False
End If
Next myCell
End With

End Sub

 

[hansjuh]

Thanks for this so far,
white means white, not none as all cells have a background color.
should I replace UsedRange with the actual range: ("A1:EC835")?


Option Explicit
Sub testme01()
Dim myCell As Range
With Worksheets("planning").Cells.Locked = True 'lock all of them
For Each myCell In .UsedRange.Cells
If myCell.Interior.ColorIndex = "white" Then
myCell.Locked = False
End If
Next myCellEnd With
End Sub

To make this thread more generic can we say Interior.ColorIndex
"white" can be replaced with other layout characteristics

 

[Dave Peterson]

Colorindexes can change with the workbook.

The number that represents white on one of my workbooks may not represent white
on another of my workbooks--or on one of yours.

select a cell that has a white fill.
hit alt-f11 (to get to the VBE)
hit ctrl-g (to see the immediate window)
type this and hit enter:
?activecell.Interior.ColorIndex


I get 2 returned.

Whatever you get, put it in this line:
If myCell.Interior.ColorIndex = ## Then

And if you want to limit the range to just A1:EC835, that's ok with me.

And you can use any property that you want in this kind of code.

I'll either use something like the immediate window to get the real value for
each property I want--or I'll record a macro just to see what excel used as a
value when I set that property.