run macro based on cell value

[fastballfreddy]

I've searched and I was able to find a way to run a macro based on a cell value but it only works if I type in the cell and doesn't work if I put a formula in the cell and it changes. Is there a way to run a macro based off a cell that has a formula?

sample of my current formula that works if I type in cell A1.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then

'Cyclones Win 2nd Game
    If Range("A3").Value = "" And Range("A4").Value = "" And Range("A1").Value = "cyclones" Then
        Range("A3").Select
        ActiveCell = "x"

'Cyclones Win 2nd Game
    ElseIf Range("B3").Value = "" And Range("B4").Value = "" And Range("A1").Value = "cyclones" Then
        Range("B3").Select
        ActiveCell = "x"

 

[Joe4]

That gets a bit tricky. There are limitations. There are a few Event Procedures that could be used in this case, but they all have their shortcomings:

Worksheet_Change - This is good because you can watch a certain range (or ranges) for changes. But as you see, it only works for manually updated data.

Worksheet_Calculate - This will run whenever any re-calculation is done anywhere on the sheet. So it would fire based on a value changing due to a formula. However, a major caveat is that it cannot tell you exactly which cell changed. It can only tell you that some cell somewhere on your sheet was re-calculated. So it is not really good for watching a specific cell change due to a formula.

However, there may be other ways around this. For example, if your formula in cell A1 is something like:
=A2+A3
you could use a Worksheet_Change event procedure, watching cells A2 and A3 for manual changes, as changes to those will change the value in A1.

 

[fastballfreddy]

Thanks for the information and confirming. I've found a workaround that will work for me. Again thanks for the response.

 

[Joe4]

You are welcome.