| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lock all cells of a certain color
Also, Locking is only effective after Protection is applied.
Ken Johnson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I lock the color palette? | Excel Discussion (Misc queries) | |||
| How do I lock the color palette? | Excel Discussion (Misc queries) | |||
| How do I lock the color palette? | Excel Discussion (Misc queries) | |||
| how do I lock the fill color in a cell | Excel Worksheet Functions | |||
| lock cells based on interior color | Excel Discussion (Misc queries) | |||
Linear Mode
