VBA code message box based on calculation on another sheet

Hi,

I have the following code that prompts a message box if a calculated cell is more or less than 5% variance. This works perfectly on the sheet I have it set to, but I would like the calculation to take place on another worksheet ("Sheet 2"), but the message box to prompt on "Sheet 1".

Also, I have several calculated cells on the same sheet, "H17", "H19" and "H20", can I repeat the same line of code below each one to check each calculation or is there an easier way?

Thanks!

Cd254


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H17") > 0.5 Or Range("h17") < -0.5 Then
MsgBox "WARNING - Calculation has exceeded limit"
End If
End Sub

Please Login or Register  to view this content.

Apply to the sheet1 code to resolve the first part. I will look at the looping now

Done without looping:

Please Login or Register  to view this content.

That's great, thanks so much!

I also have some more Subs on the same sheet, I thought I could integrate them into the same worksheet change event, but it keeps asking for an expected end sub...any ideas?!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 31 Or Target.Column = 32 Then
Call Bonus1
End If
Dim ws As Worksheet
Set ws = Sheets("Sheet2")
If ws.Range("H17") > 0.5 Or ws.Range("H17") < -0.5 Then
MsgBox "WARNING - Calculation has exceeded limit"
End If
Sub Bonus1()
Dim Key As Range
For Each cel In Sheets("Sheet1").Range("AJ14:AJ1000" & Sheets("Sheet1").Cells(Rows.Count, "AJ").End(xlUp).Row)
If cel.Value < 0 Then
MsgBox "Error! Bonus value is negative!"
Exit Sub
End If
Next cel
Sub Bonus2()
Dim Key As Range
For Each cel In Sheets("Sheet1").Range("AE14:AF1000" & Sheets("Sheet1").Cells(Rows.Count, "AJ").End(xlUp).Row)
If cel.Value < 0 Then
MsgBox "Error! Value is negative!"
Exit Sub
End If
Next cel
Sub Bonus3()
Dim Key As Range
For Each cel In Sheets("Sheet1").Range("AE14:AF1000" & Sheets("Sheet1").Cells(Rows.Count, "AJ").End(xlUp).Row)
If cel.Value < 0 Then
MsgBox "Error! Value is negative!"
Exit Sub
End If
Next cel
End Sub

You have an If statement with no else statement but you have 3 outcomes although bonus 2 and bonus 3 are the same. I have made an interpretation but can you provide a bit more direction on what you want to achieve if this doesn't help.

Maybe try:

Please Login or Register  to view this content.

Thanks, I think Bonus 3 was an oversight on my part...

Sub Bonus 1 is supposed to stop a negative value calculating in column AJ and Sub Bonus 2 is supposed to stop a negative value being inserted into columns AE or AF if that helps...

Thanks again for your help.