Code for pop-up with specific text

[wzde14]

Hi all,

I've been going through forum posts quite a bit today for some code to show a pop-up message when specific text in is any cell of one column. Probably really simple but I've gone around in circles. What I's like is for a pop-up message to display when the a formula gives me specific text.

Currently, I've been trying:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Range("AJ:AJ")
If Not Intersect(Target, rng) Is Nothing Then
If Target = "CANCEL EMAILS" Then MsgBox "Need to cancel emails"
End If
Set rng = Nothing


End Sub

It only works when the text is typed in and not when the text shows because of the formula...

The formula I'm using in AJ is: =IF(OR(AF2="By post",AF2="Primary outcome over phone"),"CANCEL EMAILS",IF(AF2="Online","N/A",IF(AF2="DNA","N/A",""))) so when any cell in this column (AJ) says "CANCEL EMAILS", I'd like a pop-up message to say "Need to cancel emails". I've tried lots of different codes, on workbook and worksheet change sheets but no joy. The tab/sheet name is "S4 Database" if needed. Any help appreciated.

I need it to pop-up whenever the formula makes the text display.

Thanks for any help with this,
Adele

 

[Scott T]

I believe recalculation of a formula does not trigger the change event to run a macro. You will need to check of AF2 changed and if it equals "By post" or "Primary outcome over phone"

 

[wzde14]

Of course! Thank you so much for your help!