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
37 (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 said:
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
:
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
