VBA code for triggering a macro based on change in cell with formula

New Contributor

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 ?

4 Replies

@Th0r_L13n 

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.

The problem is that the formula comes from a external feed, and not a sum or so from other cells
example :
=QueryJoule("Italy Baseload - All Venues", "", "1", "bid price", "1", "Quarters")
and the feed ends up to be a number

@Th0r_L13n 

You'll have to use the Worksheet_Calculate event.