Pop-Up Message on Excel Macro

[matheuscfernandes]

Hi,

I would like to create a pop-up message in Excel, where it will take any value from C12 to AA12 and compare it to a value in C68. If any value in the column between C12 and AA12 are greater than the value in C68, then a message should pop-up saying "Incorrect Value". This Macro should be run continuously, everytime a value is changed in any cells on the spreadsheet.

This is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range) - I don't know if this line is correct

Dim rDetect As Range, riDetect As Range
Set rDetect = Range("C12:AA12")


For Each riDetect In rDetect

If riDetect.Value > "C68" Then

MsgBox "AVE Value Is Incorrect"

End If
End Sub


Thanks for your help.

 

[VoG]

If you are changing those cells rather than formulas, try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rDetect As Range, riDetect As Range
Set rDetect = Range("C12:AA12")
For Each riDetect In rDetect
If riDetect.Value > Range("C68").Value Then
    MsgBox "AVE Value Is Incorrect" & riDetect.Address(False, False)
End If
Next riDetect
End Sub

 

[lenze]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$68" Then Exit Sub
Dim cl As Range
For Each cl In Range("$C$12:AA$12")
If cl > Target Then MsgBox "Cell " & cl.Address & " is out of Range"
Next cl
End Sub

lenze

 

[matheuscfernandes]

Thanks for your quick reply.

More Explanation:
What I have in the cells C12 through AA12 are average vaues of many entries above, and as I change these engtries the value in those cells change. Every time a value in any cells in the entire sheet changes, I would like for the macro to check the cells C12:AA12 to see if there is any value larger than C68

Result of both other attempts:
For some reason nothing appeared on my screen. I don't know if I'm saving it incorrectly, or if I'm running the excel spreadsheet incorrectly either. Is there someting else that needs to be done in order to run this in a pop-up mode.

 

[VoG]

You need to use a different event I think. Try

Private Sub Worksheet_Calculate()
Dim rDetect As Range, riDetect As Range
Set rDetect = Range("C12:AA12")
For Each riDetect In rDetect
If riDetect.Value > Range("C68").Value Then
    MsgBox "AVE Value Is Incorrect: " & riDetect.Address(False, False)
End If
Next riDetect
End Sub

 

[lenze]

If you remove this line

If Target.Address <> "$C$68" Then Exit Sub

the code will run when any cell in the sheet is changed manually. Note this is an Event procedure and must be in the sheet modlue. RightClick the sheet tab and choose "View Code"
I would, however, add this line

If Taret.Count > 1 Then Exit Sub

lenze

 

[matheuscfernandes]

"VoG" your code worked great, thanks.

But what I'm trying to do now is to get it to check only the average value in C12:AA12 that changes, not all of the values.

For example, if I add a value on C10 and it changes the value of C12 to something greater than the the value of cell C68 then it will alert me once about that specific cell, even if cell C13 is also above the expected value. Or if I change the value of C14 and that is smaller than the average of C68, then it doesnt alert me at all, even if the value of C13 is still greater than the value of C68.

I'm guessing I would need a code that only reads the cells betwen C12:AA12 that change, every time it changes.

Sorry for the confusion. This so far has been very, very helpful. Thanks

 

[VoG]

Perhaps this

Private Sub Worksheet_Calculate()
If WorksheetFunction.Average(Range("C12:AA12")) > Range("C68").Value Then
    MsgBox "AVE Value Is Incorrect"
End If
End Sub