 SOLVED

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

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 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

4 Replies

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

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?

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

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
best response confirmed by AnnaS550 (New Contributor)
Solution

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

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
``````

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

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