Jul 08 2021 02:48 AM
I'm trying to get a macro to run based on a change in value in a cell, but the cell contains with a formula so the standard trigger on value does not work. Any suggestions ?
Jul 08 2021 04:10 AM
The best option is to check the cells that determine the result of the formula and that are directly changed by the user.
A very simple example: let's say that cell A10 contains the formula =SUM(A2:A9), and that A2:A9 are filled by the user. Instead of checking A10, check A2:A9:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A2:A9"), Target) Is Nothing Them
...
End If
End Sub
Alternatively, you could use the Worksheet_Calculate event. This will cause a lot of overhead, though, for this event does not have a Target argument. So the code will run every time any cell on the sheet is recalculated.
Jul 08 2021 05:43 AM
Jul 08 2021 05:47 AM
Jul 08 2021 06:42 AM
You'll have to use the Worksheet_Calculate event.