lock cells with specific layout

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

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

something like:

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

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 wrote:
>
> Currently I am working on a large worksheet. I am working on the final
> stage in which i want to protect it.
> My sheet is completely colorcoded. There are about 1000 cells with a
> white background which the user may edit. All other cells (also many
> more than 1000) must be locked.
>
> I now have locked all cells. But I am not very fond of unlocking over a
> 1000 cells by hand. Is there a piece of code to automate this?
>
> something like:
>
> if cell background = white
> than cell = unlocked
> else cell = locked
>
> --
> hansjuh
> ------------------------------------------------------------------------
> hansjuh's Profile: http://www.excelforum.com/member.php...o&userid=28061
> View this thread: http://www.excelforum.com/showthread...hreadid=475772

--

Dave Peterson

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 myCell

End With
End Sub

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

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.


hansjuh wrote:
>
> 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?
>
> --
> hansjuh
> ------------------------------------------------------------------------
> hansjuh's Profile: http://www.excelforum.com/member.php...o&userid=28061
> View this thread: http://www.excelforum.com/showthread...hreadid=475772

--

Dave Peterson