Msg Box to appear if If Cell changes to certain value (cell contains formula)

[Lisa05]

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

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

Looks that the system does not accept above and it doesn't work

 

[Joe4]

You either need to loop through each cell in the range and check each one, or use a COUNTIFS formula on the range, like this:

Private Sub Worksheet_Calculate()

    Dim rng As Range
    Dim crit1 As String
    Dim crit2 As String
  
'   Set range and criteria
    Set rng = Range("A1:A12")
    crit1 = "> 3%"
    crit2 = "<= 5%"
      
    If Application.WorksheetFunction.CountIfs(rng, crit1, rng, crit2) > 0 Then
        MsgBox "Message"
    End If

End Sub