Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking all cells of a given color in a given range
Hi.
I'm trying to write a macro to look at all cells in a named range ("input_range") and 1) lock them if interior.colorindex = xlnone or 2) unlock them if interior.colorindex = 36. This routine should be called when a given cell's picklist value is changed. The cell with the picklist is actually two merged cells, and I've created a named range called "input_begin" for the picklist. Can somebody please recommend an approach? Thanks! -Michael *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking all cells of a given color in a given range
How about something like:
Dim input_range as Range With input_range if .Cells.Interior.ColorIndex=36 then ..Cells.locked = false else ..Cells.Locked = True end if end with Corey.... "Michael Siegel" wrote in message ... Hi. I'm trying to write a macro to look at all cells in a named range ("input_range") and 1) lock them if interior.colorindex = xlnone or 2) unlock them if interior.colorindex = 36. This routine should be called when a given cell's picklist value is changed. The cell with the picklist is actually two merged cells, and I've created a named range called "input_begin" for the picklist. Can somebody please recommend an approach? Thanks! -Michael *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking all cells of a given color in a given range
Thanks Corey.
This works, assuming none of the cells in "Input_Range" are merged. Unfortunately, some of them ARE merged. Assuming I cannot unmerge these cells without losing integral functionality, how can I modify your code to accomodate merged cellse? Thanks! Michael *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking all cells of a given color in a given range
Sub test()
Dim input_range As Range Dim cel As Range ' for testing merge A2:B2 Set input_range = Range("A1:A2") For Each cel In input_range With cel If .Interior.ColorIndex < 1 Or .Interior.ColorIndex = 2 Then ..MergeArea.Locked = True ElseIf .Interior.ColorIndex = 36 Then ..MergeArea.Locked = False End If End With Next End Sub Regards, Peter T "Michael Siegel" wrote in message ... Thanks Corey. This works, assuming none of the cells in "Input_Range" are merged. Unfortunately, some of them ARE merged. Assuming I cannot unmerge these cells without losing integral functionality, how can I modify your code to accomodate merged cellse? Thanks! Michael *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking all cells of a given color in a given range
Corey,
Thank you. I tested your code, and it seems to only work for the merged cells, but not for the unmerged cells. I have some of each in the range to be tested. How can I modify the code to look for BOTH types of cells in this range? Thanks. Michael *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking all cells of a given color in a given range
Try:
Sub test() Dim input_range As Range Dim cel As Range ' for testing merge A2:B2 Set input_range = Range("A1:A2") For Each cel In input_range With cel If .Interior.ColorIndex < 1 Or .Interior.ColorIndex = 2 Then ..MergeArea.Locked = True ..cel.Locked = True ElseIf .Interior.ColorIndex = 36 Then ..MergeArea.Locked = False ..cel.Locked = False End If End With Next End Sub Corey.... "Michael Siegel" wrote in message ... Corey, Thank you. I tested your code, and it seems to only work for the merged cells, but not for the unmerged cells. I have some of each in the range to be tested. How can I modify the code to look for BOTH types of cells in this range? Thanks. Michael *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking all cells of a given color in a given range
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change the color of a range of cells | New Users to Excel | |||
change the color of a range of cells | Excel Discussion (Misc queries) | |||
locking formula in cells in without locking whole sheet | Excel Discussion (Misc queries) | |||
change fill color of a range of cells based on color of a cell? | Excel Programming | |||
Color Range of Cells | Excel Programming |