Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock cells based on interior color
I have a spreadsheet which calculates values in a cell and if a threshold is
met the row is conditionally formatted to turn red. I need the cells that turn red to be locked. Can I lock the cells based on if they are red or not? Thanks, Mike |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock cells based on interior color
Testing the CF colour of a cell isn't straightforward and is described here
http://www.cpearson.com/excel/CFColors.htm Far simpler would be to test for the value that triggers the conditional format and lock the cells based on that. For example:- Sub lockcells() Dim MyRange As Range Set MyRange = Range("A1:A10") For Each C In MyRange If C.Value = 999 Then C.EntireRow.Select ActiveSheet.Unprotect Selection.Locked = True ActiveSheet.Protect End If Next End Sub This looks in A1 to a10 of the active sheet and locks the entire row if the cell value is 999. Hope this gets you going. Mike "MIke" wrote: I have a spreadsheet which calculates values in a cell and if a threshold is met the row is conditionally formatted to turn red. I need the cells that turn red to be locked. Can I lock the cells based on if they are red or not? Thanks, Mike |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock cells based on interior color
Mike,
I am testing the values in two columns (one column if the value is 100 and a second column if the value is 12.5) If either of these two values are exceeded the the row is highlighted red. Is it possible to do what you have below for two separate cell values? Could I use an IF AND statement? I had the following and it works for one column but couldnt get it to check the second value so I thought it would be easier to use the color change. The first range to check is o7:o37 (if it exceeds 100 then I need the corresponding row to lock) and the second column is Q7:Q37 (if its value exceeds 12.5 then I need the corresponding row to lock) Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value [100] Then Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True Else Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Thanks, Mike "Mike H" wrote: Testing the CF colour of a cell isn't straightforward and is described here http://www.cpearson.com/excel/CFColors.htm Far simpler would be to test for the value that triggers the conditional format and lock the cells based on that. For example:- Sub lockcells() Dim MyRange As Range Set MyRange = Range("A1:A10") For Each C In MyRange If C.Value = 999 Then C.EntireRow.Select ActiveSheet.Unprotect Selection.Locked = True ActiveSheet.Protect End If Next End Sub This looks in A1 to a10 of the active sheet and locks the entire row if the cell value is 999. Hope this gets you going. Mike "MIke" wrote: I have a spreadsheet which calculates values in a cell and if a threshold is met the row is conditionally formatted to turn red. I need the cells that turn red to be locked. Can I lock the cells based on if they are red or not? Thanks, Mike |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock cells based on interior color
Mike
You can use an offset from MyCell to do it If myCell.Value [100] Or myCell.Offset(0, 2).Value 12.5 Then Mike "MIke" wrote: Mike, I am testing the values in two columns (one column if the value is 100 and a second column if the value is 12.5) If either of these two values are exceeded the the row is highlighted red. Is it possible to do what you have below for two separate cell values? Could I use an IF AND statement? I had the following and it works for one column but couldnt get it to check the second value so I thought it would be easier to use the color change. The first range to check is o7:o37 (if it exceeds 100 then I need the corresponding row to lock) and the second column is Q7:Q37 (if its value exceeds 12.5 then I need the corresponding row to lock) Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value [100] Then Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True Else Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Thanks, Mike "Mike H" wrote: Testing the CF colour of a cell isn't straightforward and is described here http://www.cpearson.com/excel/CFColors.htm Far simpler would be to test for the value that triggers the conditional format and lock the cells based on that. For example:- Sub lockcells() Dim MyRange As Range Set MyRange = Range("A1:A10") For Each C In MyRange If C.Value = 999 Then C.EntireRow.Select ActiveSheet.Unprotect Selection.Locked = True ActiveSheet.Protect End If Next End Sub This looks in A1 to a10 of the active sheet and locks the entire row if the cell value is 999. Hope this gets you going. Mike "MIke" wrote: I have a spreadsheet which calculates values in a cell and if a threshold is met the row is conditionally formatted to turn red. I need the cells that turn red to be locked. Can I lock the cells based on if they are red or not? Thanks, Mike |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock cells based on interior color
I put the following in and it locks if it is 100 but not 12.5. Any
suggestions would be greatly appreciated. Thanks, Mike Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 100 Or myCell.Offset(0, 2).Value 12.5 Then Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True Else Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub "Mike H" wrote: Mike You can use an offset from MyCell to do it If myCell.Value [100] Or myCell.Offset(0, 2).Value 12.5 Then Mike "MIke" wrote: Mike, I am testing the values in two columns (one column if the value is 100 and a second column if the value is 12.5) If either of these two values are exceeded the the row is highlighted red. Is it possible to do what you have below for two separate cell values? Could I use an IF AND statement? I had the following and it works for one column but couldnt get it to check the second value so I thought it would be easier to use the color change. The first range to check is o7:o37 (if it exceeds 100 then I need the corresponding row to lock) and the second column is Q7:Q37 (if its value exceeds 12.5 then I need the corresponding row to lock) Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value [100] Then Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True Else Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Thanks, Mike "Mike H" wrote: Testing the CF colour of a cell isn't straightforward and is described here http://www.cpearson.com/excel/CFColors.htm Far simpler would be to test for the value that triggers the conditional format and lock the cells based on that. For example:- Sub lockcells() Dim MyRange As Range Set MyRange = Range("A1:A10") For Each C In MyRange If C.Value = 999 Then C.EntireRow.Select ActiveSheet.Unprotect Selection.Locked = True ActiveSheet.Protect End If Next End Sub This looks in A1 to a10 of the active sheet and locks the entire row if the cell value is 999. Hope this gets you going. Mike "MIke" wrote: I have a spreadsheet which calculates values in a cell and if a threshold is met the row is conditionally formatted to turn red. I need the cells that turn red to be locked. Can I lock the cells based on if they are red or not? Thanks, Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell interior color | Excel Discussion (Misc queries) | |||
Need Excel Formula/Function to color cell interior | Excel Worksheet Functions | |||
color the interior of a range | Excel Worksheet Functions | |||
Print without Interior Color | Excel Discussion (Misc queries) |