Msgbox prompted by change in value of certain cells

[Beware Wet Paint]

Hi guys I'm having a little trouble getting a macro to work in the way i intend. I want a msgbox to pop up when the value of certain cells in a column are greater than one. I've found a number of codes that *Almost* work but they seem to be based on a range e.g A1:A5... what I'd be after is for it to only concern specific cells within that range e.g A2 and A3. Any advice oh gurus of the spreadsheets? Massive thanks in advance

 

[rlv01]

Here is one example.

  Select Case Target.Address
    Case "$A$2", "$A$3", "$B$2", "$B$3"
        If Target.Value > 1 Then
            MsgBox "The value in cell " & Target.Address & " is greater than 1"
        End If
    End Select

Add the above code to the Worksheet_Change event for the worksheet you want to do this on. E.g.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Select Case Target.Address
    Case "$A$2", "$A$3", "$B$2", "$B$3"
        If Target.Value > 1 Then
            MsgBox "The value in cell " & Target.Address & " is greater than 1"
        End If
    End Select
End Sub

 

[Beware Wet Paint]

This worked PERFECTLY thank you so so much!

 

[alansidman]

Place this code in the worksheet_change event

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2:A3")) Is Nothing Then Exit Sub
    If Target.Value > 1 Then
        MsgBox "Target is greater than 1"
    End If
End Sub

 

[Beware Wet Paint]

Would there be anyway to adapt this to work on cells where the value is calculated by formula? E.g A2=B2+C2? Both codes work perfectly when the content of the range is just pure numerical input but don't when the range is calculated by formula

 

[rlv01]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCheck As Range, R As Range


    With Me
        Set rngCheck = Union(.Range("$A$2"), .Range("$A$3"), .Range("$B$2"), .Range("$B$3"))
    End With


    For Each R In rngCheck
            If R.Value > 1 Then
                MsgBox "The value in cell " & R.Address & " is greater than 1"
            End If
   Next R
End Sub

Though if you use it for too many cells, the msgbox popups could get to be annoying.

 

[Beware Wet Paint]

Awesome works a treat! It's only on a few cells so it should be manageable thank you!!