Running a macro based on cell value that is updated by formula

[GTVT06]

Hello can someone help me with this?
When I use the VBA code below, the macro will run when I update the
contents in the cell that feeds into G28's formula manually, however
the cell that feeds into "G28" a formula, and wont be keyed in
manually, so I want the macro to run when "G28" is updated
automatically due to its
formula and not due to manual entry. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("G28"), rng) Is Nothing Then
If Range("G28").Value = "Yes" Then Call
CDO_Mail_Small_Text
End If
End If
EndMacro:
End Sub


I got the code from the examples at this web site http://www.rondebruin.nl/mail/change.htm
and it says that this example is supposed to work if the cell
contents
is a formula but I cant get it to work.

 

[Jim Thomlinson]

That code will have a problem if any of the dependant of the formula in G28
are not on this sheet. Target.Dependents only returns the dependent cells on
the current sheet. Just one possible reason things might not be working...

 

[Jim Cone]

See another response in your identical post in the public.excel group.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"GTVT06" <[email protected]>
wrote in message Hello can someone help me with this?
When I use the VBA code below, the macro will run when I update the
contents in the cell that feeds into G28's formula manually, however
the cell that feeds into "G28" a formula, and wont be keyed in
manually, so I want the macro to run when "G28" is updated
automatically due to its
formula and not due to manual entry. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("G28"), rng) Is Nothing Then
If Range("G28").Value = "Yes" Then Call
CDO_Mail_Small_Text
End If
End If
EndMacro:
End Sub


I got the code from the examples at this web site http://www.rondebruin.nl/mail/change.htm
and it says that this example is supposed to work if the cell
contents
is a formula but I cant get it to work.