If Cell changes to certain value then Msg Box to appear-CodePudding

I have couple of Cells Range A1:A12 that calculate percentage. I would like to set up a role that if percentage in those cell is between 3% and 5% Msg box appears. I am trying following code:

Private Sub Worksheet_Calculate()
If Range("A1:A12").Value > 2% And  Range("A1:A12").Value <= 5% Then
MsgBox "Message"
Else
Exit Sub
End Sub

Could someone help me to adjust the code so that it works, looks as it doesn't accept it at all.

CodePudding user response：

I'd probably go about this with the following code.

Private Sub Worksheet_Calculate()
    
    Dim cellsbetween As Long, c As Range
    cellsbetween = 0
    
    For Each c In Range("A1:A12").Cells
        If c.Value > 0.02 And c.Value <= 0.05 Then cellsbetween = cellsbetween   1
    Next
    
    If cellsbetween > 0 Then MsgBox "There are " & cellsbetween & " cell(s) in the 2% - > 5% range"

End Sub

This will only display a pop-up once, per scan. There are still issues though. If you don't do something to change the value(s) that are in the 3%-5% range, then you're going to get very annoyed with that pop-up.

One alternative, is to monitor the source cells (the cells that change from a users input, not from a recalc) and only run the above code if those cells change - detected by a Worksheet_Change() event.

Finally, here is another way to achieve your goal that uses a more complex method and a more robust way to recognise a cells value (when it contains a formula) changing.

Private Sub Worksheet_Calculate()
    
    Dim cellsbetween As Long, c As Range
    cellsbetween = 0
    Application.EnableEvents = False
    For Each c In Range("A1:A12").Cells
        If c.Value > 0.02 And c.Value <= 0.05 And c.Value <> c.Offset(0, 51).Value Then
            cellsbetween = cellsbetween   1
            MsgBox "Cell " & c.Address & " is in the 2% - > 5% range"
        End If
        c.Offset(0, 51) = c.Value
    Next
    Application.EnableEvents = True

End Sub

This method stores the values in column AZ (you can hide this column if you want), so that it can test that the value has actually changed. This way, no matter how many times the sheet is recalculated, the pop up will only appear each time the actual value changes.

CodePudding user response：

Try this:

    Dim row As Range

    For Each row In Range("A1:A12").Rows

         If Val(row.Cells(1, 1).Value) > 0.03 And Val(row.Cells(1, 1).Value) <= 0.05 Then
    
            MsgBox ("message")

        End If

    Next

Thats the correct way of looping, you can not compare a whole range to a single value, if the range contains more than one cell it is an array and you need to check each inndividual cell.

The code has a problem when the decimal separator is a coma (which is my case) but works well when it is a point (I hope it is your case).