Run macro as soon as specific text is added to a cell with numeric value already within

[brumby]

Hi all,

Ive managed to get work worksheet to only calculate a cell if text is added. This allows me to plot a suppliers delivery and only consider it once I add "- arrived " EG below

A1 = 1000
B1 ( =A1 ) = 0

When delivery arrives

A1 = "1000 arrived"
B1 NOW RETURNS = 1000

As soon as I add "*ar*" into A1 ( ar should cover any typos etc ), I would like to run "Macro1"

Is this possible?

Many Thanks!!!

 

[mart37]

You can use an event.
Events are placed on the sheet they belong.
Not in a module!

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, ActiveSheet.Columns("A")) Is Nothing Then Exit Sub
If Target Like "*ar*" Then Macro1
End Sub

 

[brumby]

Supoerb, many thanks, I want to build it into the below is this possible? So only run Macro cpy if the range n6:w500 contains the "ar" text?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("n6:w500")) Is Nothing Then Call cpy
If Not Intersect(Target, Range("i6:k500")) Is Nothing Then Call cpy1
If Not Intersect(Target, Range("AB6:AB500")) Is Nothing Then Call cpy2
Application.EnableEvents = True
End Sub

 

[mart37]

If Not Intersect(Target, Range("n6:w500")) Is Nothing And Target Like "*ar*" Then Call cpy

 

[brumby]

superb thank you, 1 very very very last bit of help as I am now getting a problem if I undo or delete something.

So if I delete or undo anything in Range("n6:w500") I would like to call cpy2