Can anyone help me with the following code? I am trying to lock a row based
on the results of two formulas. If either one of the results are greater
then a certain value then the row will lock. The following code locks the
cell if the first condition is met but not the second. Is there another way
I could get it to check the second condition. Any help 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

What's in the second cell for one of the failing rows?

It looks like your code should work (well, to me anyway).

But I would move the .protect line under the ws_exit: line.

MIke wrote:

Can anyone help me with the following code? I am trying to lock a row based
on the results of two formulas. If either one of the results are greater
then a certain value then the row will lock. The following code locks the
cell if the first condition is met but not the second. Is there another way
I could get it to check the second condition. Any help 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

--

Dave Peterson

Dave,
Do you mean the cell used for the second condition? If so, it has a formula
(o/p) in it. If the result exceeds 12.5 I need the row to lock. I have
plugged in samples exceeding the 12.5 and it doesnt lock. The row only locks
if I go over the first condition.

Basically, the sheet is set up to calculate the total in column O using
values in columns b to n. In Column P you input hrs worked and Column Q
calculates the number per hr.

Thanks for the help.

Mike

"Dave Peterson" wrote:

What's in the second cell for one of the failing rows?

It looks like your code should work (well, to me anyway).

But I would move the .protect line under the ws_exit: line.

MIke wrote:

Can anyone help me with the following code? I am trying to lock a row based
on the results of two formulas. If either one of the results are greater
then a certain value then the row will lock. The following code locks the
cell if the first condition is met but not the second. Is there another way
I could get it to check the second condition. Any help 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

--

Dave Peterson

Just a guess.

My bet is that you have a mistake in your layout.

What columns did you mean to check? Column O and the column 2 to its right
(column Q)???

When mycell is in column O, then myCell.Offset(0, 2) is in column Q.

But that's just a guess.

MIke wrote:

Dave,
Do you mean the cell used for the second condition? If so, it has a formula
(o/p) in it. If the result exceeds 12.5 I need the row to lock. I have
plugged in samples exceeding the 12.5 and it doesnt lock. The row only locks
if I go over the first condition.

Basically, the sheet is set up to calculate the total in column O using
values in columns b to n. In Column P you input hrs worked and Column Q
calculates the number per hr.

Thanks for the help.

Mike

"Dave Peterson" wrote:

What's in the second cell for one of the failing rows?

It looks like your code should work (well, to me anyway).

But I would move the .protect line under the ws_exit: line.

MIke wrote:

Can anyone help me with the following code? I am trying to lock a row based
on the results of two formulas. If either one of the results are greater
then a certain value then the row will lock. The following code locks the
cell if the first condition is met but not the second. Is there another way
I could get it to check the second condition. Any help 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

--

Dave Peterson


--

Dave Peterson

I tested OK. Why use a calculation event which will fire with EACH
calculation. Why not a worksheet_change event that fires with col O or col Q

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MIke" wrote in message
...
Can anyone help me with the following code? I am trying to lock a row
based
on the results of two formulas. If either one of the results are greater
then a certain value then the row will lock. The following code locks the
cell if the first condition is met but not the second. Is there another
way
I could get it to check the second condition. Any help 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

Don,

I am a novice to visual basic. I used the calculation event originally
because I just wanted to monitor the initial event in Column O but forgot
about the requirement in Column Q. Could were I have it located be the
reason it is not working for me?

Thanks fo the help,
Mike



"Don Guillett" wrote:

I tested OK. Why use a calculation event which will fire with EACH
calculation. Why not a worksheet_change event that fires with col O or col Q

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MIke" wrote in message
...
Can anyone help me with the following code? I am trying to lock a row
based
on the results of two formulas. If either one of the results are greater
then a certain value then the row will lock. The following code locks the
cell if the first condition is met but not the second. Is there another
way
I could get it to check the second condition. Any help 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

As has been pointed out, your code works. If desired, send a workbook to my
private email below along with complete instructions as to your desires.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MIke" wrote in message
...
Don,

I am a novice to visual basic. I used the calculation event originally
because I just wanted to monitor the initial event in Column O but forgot
about the requirement in Column Q. Could were I have it located be the
reason it is not working for me?

Thanks fo the help,
Mike



"Don Guillett" wrote:

I tested OK. Why use a calculation event which will fire with EACH
calculation. Why not a worksheet_change event that fires with col O or
col Q

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MIke" wrote in message
...
Can anyone help me with the following code? I am trying to lock a row
based
on the results of two formulas. If either one of the results are
greater
then a certain value then the row will lock. The following code locks
the
cell if the first condition is met but not the second. Is there
another
way
I could get it to check the second condition. Any help 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