email notification on change in cell text

[yssteve]

Hi guys,

have been trying to solve this problem but thought i'll need some help afterall..

i've been trying to get an email notification to be sent out after a range(H:H) has been updated to different statuses (default as ACTV, needs email when changes to SUSP or HALT) via a formula automatically. the change or on calculate events doesn't work..i tried an if or statement but it didn't work too

thanks in advance!

 

[Arcticwarrio]

you should be able to adapt this and trigger it with the onchange script

http://www.mrexcel.com/forum/showthread.php?t=555359&highlight=send+email

 

[yssteve]

thanks for the quick reply! that helps alot with the email firing part but i'm still quite lost about the following..

when the status (Column H via the formula) changes from "ACTV" to "SUSP" or "HALT", notification to be sent, but when "SUSP" or "HALT" back to "ACTV", no action is needed..

any input?

thanks again guys!

 

[Arcticwarrio]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
    If Target.Cells.Count <> 1 Then Exit Sub
If ch = "SUSP" Then EMAIL_SCRIPT
If ch = "HALT" Then EMAIL_SCRIPT
If ch = "ACTV" Then Exit Sub
End If
End If
End Sub