How to create a pop-up reminder when a certain cell is chosen but dependent on a value of another cell?

[jenniferTTD]

Hi,

I would like to ask:

How do you create a pop-up reminder when a certain cell is chosen but dependent on a value of another cell?

To be specific, we have the type of service cell J2. "Transportation" and "Guiding" is part of a drop-down menu and every time they are chosen, there is a pop-up reminder. See below VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("J2:J54")) Is Nothing Then Exit Sub
If Target.Value = "Transportation" Then
MsgBox "TRANSPORTATION: Remember to include VAT, as well as any additional costs, such as toll and parking fees, ferry tickets, water bottles, etc."
ElseIf Target.Value = "Guiding" Then
MsgBox "GUIDING: Remember to include any additional costs, such as entrance fees, tickets, city passes, coffee/tea/water, snacks, etc."Ho
End If
End Sub

My question is: Can we move the pop-up reminder to appear in a different cell, in this case, where the price should be written (Range: N2:N54)? Every time a price is written in the price range (N2:N54) the pop-up reminder should appear GIVEN that the type of service (range J2:J54) is only either "Transportation" or "Guiding"?


I appreciate any help I can get. Sorry, I am not well versed with excel and it seems complicated since there is a condition to be met. Thank you!

 

[jmacleary]

Something like this should work (untested)

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("N2:N54")) Is Nothing Then Exit Sub
If Range("J" & Target.Row).Value = "Transportation" Then
MsgBox "TRANSPORTATION: Remember to include VAT, as well as any additional costs, such as toll and parking fees, ferry tickets, water bottles, etc."
ElseIf Range("J" & Target.Row).Value = "Guiding" Then
MsgBox "GUIDING: Remember to include any additional costs, such as entrance fees, tickets, city passes, coffee/tea/water, snacks, etc."
End If
End Sub

 

[jenniferTTD]

Something like this should work (untested)

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("N2:N54")) Is Nothing Then Exit Sub
If Range("J" & Target.Row).Value = "Transportation" Then
MsgBox "TRANSPORTATION: Remember to include VAT, as well as any additional costs, such as toll and parking fees, ferry tickets, water bottles, etc."
ElseIf Range("J" & Target.Row).Value = "Guiding" Then
MsgBox "GUIDING: Remember to include any additional costs, such as entrance fees, tickets, city passes, coffee/tea/water, snacks, etc."
End If
End Sub

It worked exactly like how I wanted it to be! Thank you so much!!

 

[jmacleary]

You are very welcome and thanks for the feedback.