run macro based on cell value

fastballfreddy

Board Regular
Joined
Jan 13, 2015
Messages
64
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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"
 
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.
 
Upvote 0
Solution
Thanks for the information and confirming. I've found a workaround that will work for me. Again thanks for the response.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top