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

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

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

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

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