Protecting cells by coluor

[st120869]

I have used 3 colours in my spreadsheet 1 for headings /titles, 1 for data entry and 1 for calculated fields. How can I protect all cells with a specific colour to prevent users from changing contents / formula

[Rowan Drummond]

Protects all cells which are coloured Light Yellow (colour index 36):

Sub PrtcColor()
Dim cell As Range
With ActiveSheet
.Unprotect
For Each cell In .UsedRange
If cell.Interior.ColorIndex = 36 Then
cell.Locked = True
Else
cell.Locked = False
End If
Next cell
.Protect
End With
End Sub

Hope this helps
Rowan

st120869 wrote:
> I have used 3 colours in my spreadsheet 1 for headings /titles, 1 for
> data entry and 1 for calculated fields. How can I protect all cells
> with a specific colour to prevent users from changing contents /
> formula
>
>

[st120869]

Great, thanks this works. As an aside it will not work if any cells are merged - took a while to work this one out as I kept on getting an error message.

[Rowan Drummond]

You're welcome. You will find a lot of things don't work with merged
cells. As a result I never use them.

Regards
Rowan

st120869 wrote:
> Great, thanks this works. As an aside it will not work if any cells are
> merged - took a while to work this one out as I kept on getting an
> error message.
>
>