lock cells based on formula

[MIke]

I am trying to lock a range of cells based on two conditions. The first
condition is if the value in range (o7:037) > 8. The second condition is if
the value in range Q7:Q37 > 12. If either of these condition are met I need
the corresponding range B:N to lock. I have the following code supplied to
me by Gord Dibben and it works great when evaluating one condition. He has
suggested using IF OR statements to evaluate the two conditions. I am not
that familiar with VBA code and I can not get the code to work. Can anyone
point me in the right direction?

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 > 8 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

 

[Pranav Vaidya]

Hi Mike,

I think you want the cells to be locked if any one condition is met. In that
case change

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!

" If myCell.Value > 8 Then" as

" If myCell.Value > 8 OR myCell.Value > 12 Then "

This change will check for both the conditions and will lock the cells if
either of the conditions are met. If you want both the conditions to be met,
then use AND instead of OR.

HTH,

 

[MIke]

Pranav,

How do I differentiate the two ranges I am checking? Right now it is only
checking range O7:37 to see if the value is > 8 The second condition is to
check Range Q7:Q37 to see if it is >12. When I changed my code it didnt lock
when my values in range Q7:Q37 exceeded 12.

Thanks for the help,
MIke

 

[IT Dev Guy]

Mike,

I think you can just do this (below). What you do is check each cell in
your first range and, if it matches your criteria, lock the range specified;
if it doesn't match, then you check the corresponding cell in column Q and
see if it matches the criteria and, if so, you lock this time. If neither
matches, the range specified is unlocked.
----------------------------------------------------------------------
Private Sub Worksheet_Calculate()
Dim myCell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="justme"
Dim lockIt As Boolean
For Each myCell In Range("O7:O37")
lockIt = False
If myCell.Value > 8 Then
lockIt = True
ElseIf myCell.Offset(0, 2).Value > 12 Then
lockIt = True
End If
Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = lockIt
Next myCell
ActiveSheet.Protect Password:="justme"

ws_exit:
Application.EnableEvents = True
End Sub

 

[MIke]

IT Dev Guy,
I am not sure if I am doing something wrong but when I plugged the code into
the sheet it only locked if the first condition was met o737>8. I need to
add another value into column p to generate the value in column Q. Could
this be the problem?

Basically I am checking the sums in columns B:n and if they are greater then
"8" the cells lock and regardless of whether the cells lock or not the userr
puts how many hours they worked in column p. Q is then calculate by dividing
O/P. So there are two seperate calculations going on at two different times.
Is this a problem?

I hope this makes sense.

Thanks again,
Mike