RE: lock cells based on interior color

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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
Active***.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
Active***.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
Active***.Unprotect
Selection.Locked = True
Active***.Protect
End If
Next
End Sub

This looks in A1 to a10 of the active *** and locks the entire row if the
cell value is 999. Hope this gets you going.

Mike

"MIke" wrote:

I have a spread*** 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
.

Quantcast