If statement with macro (advanced problem!)

[bbot23]

I google'd the code to run a macro depending on a met condition, but I can't seem to make it work properly. Can anyone help? Here's the code that I got:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("AG6")) Is Nothing Then
Exit Sub
End If

If Range("AG6") = 1 Then
SomeMacro
End If

End Sub

Sub SomeMacro()
MsgBox ("Hi!")
End Sub

If I change AG6 to 1 manually, the above macro works fine. But I have a formula in AG6 that is "=IF(AF6=1, 1, ""). Is there any way to launch a macro if the AG6 cell becomes 1 from the formula (ie formula is TRUE)?

 

[bertie]

Try using Worksheet_Calculate rather than Worksheet_Change.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Calculate()
   [COLOR=darkblue]If[/COLOR] Range("AG6") = 1 [COLOR=darkblue]Then[/COLOR]
      SomeMacro
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
[COLOR=darkblue]Sub[/COLOR] SomeMacro()
MsgBox ("Hi!")
End Sub

NB both procedure should reside in the same sheet module.

 

[Marcelo Branco]

Hi,

Maybe this i've just seen in the internet

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("AG6").DirectPrecedents) Is Nothing Then
        If Range("AG6") = 1 Then
            SomeMacro
        End If
    End If
 
End Sub
 
Sub SomeMacro()
    MsgBox ("Hi!")
End Sub

M.

 

[Marcelo Branco]

I think in your case you have to use Precedents not DirectPrecedents

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("AG6").Precedents) Is Nothing Then
        If Range("AG6") = 1 Then
            SomeMacro
        End If
    End If
 
End Sub
 
Sub SomeMacro()
   MsgBox ("Hi!")
End Sub

M.