lock all cells of a certain color
 

Haven't found this yet in previous threads, but I'm sure i missed it.

I am trying to Lock all cells that are a certain color as follows:

Sub lock_all_tan()

For Each s In Sheets
For Each c In Cells
If c.ColorIndex = 40 Then
c.Locked = False
c.FormulaHidden = False
End If
Next
Next

End Sub

The above doesn't work at all, appreciate any help. Thanks, Jason

lock all cells of a certain color
 

TxRaistlin wrote:
Haven't found this yet in previous threads, but I'm sure i missed it.

I am trying to Lock all cells that are a certain color as follows:

Sub lock_all_tan()

For Each s In Sheets
For Each c In Cells
If c.ColorIndex = 40 Then
c.Locked = False
c.FormulaHidden = False
End If
Next
Next

End Sub

The above doesn't work at all, appreciate any help. Thanks, Jason

Hi Jason,

Change the two False values to True. False Unlocks and UnHides.

Ken Johnson

lock all cells of a certain color
 

Sub lock_all_tan()

Dim s as Worksheet, c as Range
For Each s In WorkSheets
s.Activate
s.Unprotect Password:="ABC"
For Each c In s.UsedRange.Cells
If c.ColorIndex = 40 Then
c.Locked = False
c.FormulaHidden = False
End If
Next
s.Protect Password:="ABC"
Next


You will need to then protect the sheet for it to have any effect
End Sub

--
Regards,
Tom Ogilvy


"TxRaistlin" wrote:

Haven't found this yet in previous threads, but I'm sure i missed it.

I am trying to Lock all cells that are a certain color as follows:

Sub lock_all_tan()

For Each s In Sheets
For Each c In Cells
If c.ColorIndex = 40 Then
c.Locked = False
c.FormulaHidden = False
End If
Next
Next

End Sub

The above doesn't work at all, appreciate any help. Thanks, Jason

lock all cells of a certain color
 

Also, Locking is only effective after Protection is applied.

Ken Johnson

lock all cells of a certain color
 

I received the following error:

Object doesn't support this property.

I made a mistake in my original description, I am wanting to unlock only
those cells that are tan, everything else stays locked.

Thanks,

Jason


"Tom Ogilvy" wrote:

Sub lock_all_tan()

Dim s as Worksheet, c as Range
For Each s In WorkSheets
s.Activate
s.Unprotect Password:="ABC"
For Each c In s.UsedRange.Cells
If c.ColorIndex = 40 Then
c.Locked = False
c.FormulaHidden = False
End If
Next
s.Protect Password:="ABC"
Next


You will need to then protect the sheet for it to have any effect
End Sub

--
Regards,
Tom Ogilvy


"TxRaistlin" wrote:

Haven't found this yet in previous threads, but I'm sure i missed it.

I am trying to Lock all cells that are a certain color as follows:

Sub lock_all_tan()

For Each s In Sheets
For Each c In Cells
If c.ColorIndex = 40 Then
c.Locked = False
c.FormulaHidden = False
End If
Next
Next

End Sub

The above doesn't work at all, appreciate any help. Thanks, Jason

lock all cells of a certain color
 

If you are using conditional formatting to color the cells,
then none of the code will work. If you are not using CF then
in Tom's code change...

If c.ColorIndex = 40 Then

to

If c.Interior.ColorIndex = 40 Then
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"TxRaistlin"

wrote in message
I received the following error:
Object doesn't support this property.

I made a mistake in my original description, I am wanting to unlock only
those cells that are tan, everything else stays locked.
Thanks,
Jason


"Tom Ogilvy" wrote:
Sub lock_all_tan()
Dim s as Worksheet, c as Range
For Each s In WorkSheets
s.Activate
s.Unprotect Password:="ABC"
For Each c In s.UsedRange.Cells
If c.ColorIndex = 40 Then
c.Locked = False
c.FormulaHidden = False
End If
Next
s.Protect Password:="ABC"
Next


You will need to then protect the sheet for it to have any effect
End Sub
--
Regards,
Tom Ogilvy

lock all cells of a certain color
 

Sub unlock_all_tan()

Dim s As Worksheet, c As Range
For Each s In Worksheets
s.Activate
s.Unprotect Password:="ABC"
s.Cells.Locked = True
s.Cells.formulaHidden = True
For Each c In s.UsedRange.Cells
If c.Interior.ColorIndex = 40 Then
c.Locked = False
c.FormulaHidden = False
End If
Next
s.Protect Password:="ABC"
Next
End Sub

--
Regards,
Tom Ogilvy


"TxRaistlin" wrote:

I received the following error:

Object doesn't support this property.

I made a mistake in my original description, I am wanting to unlock only
those cells that are tan, everything else stays locked.

Thanks,

Jason


"Tom Ogilvy" wrote:

Sub lock_all_tan()

Dim s as Worksheet, c as Range
For Each s In WorkSheets
s.Activate
s.Unprotect Password:="ABC"
For Each c In s.UsedRange.Cells
If c.ColorIndex = 40 Then
c.Locked = False
c.FormulaHidden = False
End If
Next
s.Protect Password:="ABC"
Next


You will need to then protect the sheet for it to have any effect
End Sub

--
Regards,
Tom Ogilvy


"TxRaistlin" wrote:

Haven't found this yet in previous threads, but I'm sure i missed it.

I am trying to Lock all cells that are a certain color as follows:

Sub lock_all_tan()

For Each s In Sheets
For Each c In Cells
If c.ColorIndex = 40 Then
c.Locked = False
c.FormulaHidden = False
End If
Next
Next

End Sub

The above doesn't work at all, appreciate any help. Thanks, Jason

lock all cells of a certain color
 

Thanks!

"Tom Ogilvy" wrote:

Sub unlock_all_tan()

Dim s As Worksheet, c As Range
For Each s In Worksheets
s.Activate
s.Unprotect Password:="ABC"
s.Cells.Locked = True
s.Cells.formulaHidden = True
For Each c In s.UsedRange.Cells
If c.Interior.ColorIndex = 40 Then
c.Locked = False
c.FormulaHidden = False
End If
Next
s.Protect Password:="ABC"
Next
End Sub

--
Regards,
Tom Ogilvy


"TxRaistlin" wrote:

I received the following error:

Object doesn't support this property.

I made a mistake in my original description, I am wanting to unlock only
those cells that are tan, everything else stays locked.

Thanks,

Jason


"Tom Ogilvy" wrote:

Sub lock_all_tan()

Dim s as Worksheet, c as Range
For Each s In WorkSheets
s.Activate
s.Unprotect Password:="ABC"
For Each c In s.UsedRange.Cells
If c.ColorIndex = 40 Then
c.Locked = False
c.FormulaHidden = False
End If
Next
s.Protect Password:="ABC"
Next


You will need to then protect the sheet for it to have any effect
End Sub

--
Regards,
Tom Ogilvy


"TxRaistlin" wrote:

Haven't found this yet in previous threads, but I'm sure i missed it.

I am trying to Lock all cells that are a certain color as follows:

Sub lock_all_tan()

For Each s In Sheets
For Each c In Cells
If c.ColorIndex = 40 Then
c.Locked = False
c.FormulaHidden = False
End If
Next
Next

End Sub

The above doesn't work at all, appreciate any help. Thanks, Jason