Solved: Solved....Locking Cells?

[davisbrc]

I am a VB noob, so I am wondering what needs to be changed in this code to allow me to lock all the cells in every sheet, except for cells that are yellow.

[vba]Dim wSht As Worksheet
Dim allwShts As Sheets
Set allwShts = Worksheets
On Error Resume Next
For I = 1 To Sheets.Count
Sheets(I).Activate
ActiveSheet.Unprotect Password:=""
Application.ScreenUpdating = False
Cells.Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas).Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.Protect Password:=""
Application.ScreenUpdating = True
Next I[/vba]

[lucas]

Try this:
[VBA]Option Explicit
Sub ColLock()
Dim Cel As Range, c As Range, Col As Long, Lkd As Boolean
Dim sh As Worksheet
Set Cel = Range("A1")
Application.ScreenUpdating = False
Lkd = Cel.Locked
For Each sh In Worksheets
sh.Unprotect
For Each c In sh.UsedRange
If c.Interior.ColorIndex = 6 Then
c.Locked = Not Lkd
End If
Next
Next
For Each sh In Worksheets
sh.Protect
Next
Application.ScreenUpdating = True
End Sub
[/VBA]
example attached

[mdmackillop]

Why not create a Style for fill colour and "not locked". Saves the problem arising in the future.

[davisbrc]

Thanks Lucas and MD for the quick responses.

I used the code Lucas provided and it worked in my spreadsheet after I got rid of a couple merged cells, which gave me a runtime error code of '1004'

Thanks for your help.

[lucas]

I suggest you avoid merged cells at all costs if possible, this is a perfect example of why. Use center across selection instead.

[lucas]

Be sure to mark your thread solved if you have your solution. Use the thread tools at the top of the page.