Message box macro not working

[SCOTTWHITTAKER2333]

I have a macro for a new wt. form that I want to display a message if a range is below a negative value of another cell. But I can't seem to get it to work. I think the problem is in the part that I highlighted in red below. This is what I have:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'
' hold box Macro
' Macro recorded 5/28/2010 by SCOTT.WHITTAKER
'
'
If (Range("F50:Y68").Value < 0 - Range("H14")) Then
MsgBox "Product has failed Individual Weights, All product must be held from last acceptable check until next acceptable check. Please click the remove button for the affected Group to remove the affected check from the final average."

End If
End Sub

 

[Andrew Poulsom]

I don't know but maybe Range("F50:Y68") should be Target. Applying the Value property to a multicell range doesn't make a lot of sense.

 

[SCOTTWHITTAKER2333]

So shoud I list each Cell seperatly? Boy I hope not that would take forever. And I'm not really all the great with macros in the first place. Basicly I need the message box to display whenever data is enter in the range that is less than the value of H14 as a negative.

 

[Andrew Poulsom]

So shoud I list each Cell seperatly?

No, Target is the cell that changed.

 

[SCOTTWHITTAKER2333]

So i'm sure that I'm just doing this correctly but I reworte it like this but its still not working:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


Target = Range("F50:Y68")
If (Target < 0 - Range("H14")) Then
MsgBox "Product has failed Individual Weights, All product must be held from last acceptable check until next acceptable check"
End If
End Sub

 

[SCOTTWHITTAKER2333]

I meant wrong not correctly. Sorry.

 

[Andrew Poulsom]

I'm confused by "if a range is below a negative value of another cell", but Target (the cell that has changed) is passed to the procedure as an argument. For a simple test try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Application.Intersect(Target, Range("F50:Y68")) Is Nothing Then Exit Sub
    If (Target.Value < Range("H14").Value) Then
        MsgBox "Product has failed Individual Weights, All product must be held from last acceptable check until next acceptable check"
    End If
End Sub

The code belongs in the module for ThisWorkbook.

 

[SCOTTWHITTAKER2333]

That is almost perfect except it is not happening when data is entered.
Oh the thing with cell H14 an a negative is just that I need the cell to be positive for some other formulas so I was trying to say 0-H14. Anyway I can get around that part. I think the _worksheetchange is the part that need to change but I tried _sheetselectionchange and it said that the:

If (Target.Value < Range("H14").Value) Then

part was causing a runtime error13 "type mismatch"
Also I need it to work only once for each cell in the range.
Thanks you have already been a huge help!

 

[Andrew Poulsom]

I tested the code before posting and it fired if I changed a cell in the range F50:Y68 (on any worksheet in the workbook). What's in H14 that could be causing a type mismatch?

 

[SCOTTWHITTAKER2333]

AaaHaa thats the problem. When data is entered into my spreadsheet the data is actually entered into a different column and formulas in the range f50:y68 take the target wt and tare wt out calculate the amount over or under. As for h14 it is a formula that displays the max lower limit based on data entered into o21 and p21 about the target wt and measurement type like "lbs" or "oz". Oh by the way, GOOD LORD!! 0337AM ? When do you sleep? So Anyways I'm not sure witch way to go now but i also tried _calculate and couldn't that to work.