How to get a pop-up message to show only for the intended column.

[AnnaS550]

Hi.

I am working on a sheet where i have 3 different pop-up messages to show for certain values in cells in a certain column.

E.g. a value between 1-5 generates one message, a value between 6-10 generates a different message...

My problem is that this message seems to pop up for every cell in that same row afterwards. How can I change the code to get it to pop up only for that column?

I have spend enough time on this :see_no_evil_monkey: and hope someone can help me to get forward.

Regards Anna

My code below:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim total As Double

    Application.Calculate

    total = Sheet1.Range("E" & Target.Row).Value

    If (total >= 1) And (total <= 5) Then

        Call MsgBox("Message 1-5.", vbOKOnly, "Evaluation")

    End If

     If (total >= 6) And (total <= 10) Then

        Call MsgBox("Message 6-10.", vbOKOnly, "Evaluation")

    End If

       If (total >= 11) And (total <= 15) Then

        Call MsgBox("Message 11-15.", vbOKOnly, "Evaluation")

    End If

End Sub

[#2]

@AnnaS550 

Is column E the column you want to look at? If not, which one?

And does this column contain a formula or is it filled in by the user?

[#3]

Thank you Hans for quick response,
Yes the column I want to look at is E and it contains the formula (=Cx*Dx) with x being the row...
So column C and D is filled by the user and column E is meant to calculate the product of the factors in C/D.
/ Anna

[#4 · Accepted solution]

@AnnaS550 

Here you go:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim total As Double
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Range("C:D"), Target) Is Nothing Then
        Application.Calculate
        Application.EnableEvents = False
        total = Sheet1.Range("E" & Target.Row).Value
        If total >= 1 And total <= 5 Then
            Call MsgBox("Message 1-5.", vbOKOnly, "Evaluation")
        ElseIf total <= 10 Then
            Call MsgBox("Message 6-10.", vbOKOnly, "Evaluation")
        ElseIf total <= 15 Then
            Call MsgBox("Message 11-15.", vbOKOnly, "Evaluation")
        End If
        Application.EnableEvents = True
    End If
End Sub

[#5]

Yes! Thank you so much for your help. Much appreciated!
/ Anna