how to combine two and more worksheet calculate event

[Summer7sun]

Private Sub Worksheet_Calculate()

Dim aRng As Range
Dim aCell As Range


Set aRng = Range("AE9:AE92")



For Each aCell In aRng
If aCell.Value = "Yes" Then
Call HitIt
Exit Sub
End If
Next
For Each aCell In aRng
If aCell.Value = "No" Then
Call HitIt
Exit Sub
End If
Next

End Sub

Private Sub Worksheet_Calculate()
Dim bRng As Range
Dim bCell As Range

Set bRng = Range("BH9:BH92")



For Each bCell In bRng
If bCell.Value = "Yes To Do" Then
Call HitIt2
Exit Sub
End If
Next
For Each bCell In bRng
If bCell.Value = "No Dont" Then
Call HitIt2
Exit Sub
End If
Next
End Sub

these code work only if one of them is in the worksheet calculate event ... The cell change is due to a formula .... and how to combine these code and in future how to combine a new one similar to this please help

 

[JLGWhiz]

See if this will work for you.

Private Sub Worksheet_Calculate()
Dim aRng As Range
Dim aCell As Range
Dim bRng As Range
Dim bCell As Range
Set aRng = Range("AE9:AE92")
    For Each aCell In aRng
        If aCell.Value = "Yes" Then
            Call HitIt
            GoTo PART2:
        End If
    Next
    For Each aCell In aRng
        If aCell.Value = "No" Then
            Call HitIt
            GoTo PART2:
        End If
    Next
PART2:
Set bRng = Range("BH9:BH92")
    For Each bCell In bRng
        If bCell.Value = "Yes To Do" Then
            Call HitIt2
            Exit Sub
        End If
    Next
    For Each bCell In bRng
        If bCell.Value = "No Dont" Then
            Call HitIt2
            Exit Sub
        End If
    Next
End Sub

 

[JoeMo]

You can only have one event procedure in a sheet. Just combine the two - like below. This could be shortened but should work fine like this.

Private Sub Worksheet_Calculate()
Dim aRng As Range
Dim aCell As Range
Dim bRng As Range
Dim bCell As Range
Set aRng = Range("AE9:AE92")
For Each aCell In aRng
    If aCell.Value = "Yes" Then
    Call HitIt
    Exit For
    End If
Next aCell
For Each aCell In aRng
    If aCell.Value = "No" Then
    Call HitIt
    Exit For
    End If
Next aCell
Set bRng = Range("BH9:BH92")
For Each bCell In bRng
    If bCell.Value = "Yes To Do" Then
    Call HitIt2
    Exit For
    End If
Next bCell
For Each bCell In bRng
    If bCell.Value = "No Dont" Then
    Call HitIt2
    Exit For
    End If
Next bCell
End Sub

 

[JLGWhiz]

@JoeMo, I could be wrong, but it looked to me like if the called macro ran for the Yes match, then the OP wanted to forego the second loop for that range. Using the 'Exit For' will allow the second loop to run for the same range.
JLG

 

[JoeMo]

JLGWhiz - I didn't look at it closely - I think you are right.

 

[JLGWhiz]

JLGWhiz - I didn't look at it closely - I think you are right.

We'll find out if the OP responds, I suppose.

 

[Summer7sun]

Thank you guys for your code, I got stuck with some extremely important work so could not update the result and it took a bit time. I would be able to test it in next 12 hrs and get back to you.... Thanks for your time JLGWhiz and JoeMo

 

[Summer7sun]

Thanks guys working with little tweaks

 

[JLGWhiz]

Thanks guys working with little tweaks

Thanks for the feedback,
regards, JLG