Active macro when a formula changes a cell to True

Question

I am looking for some code to activate another macro when the cell changes to "True" (Cell Q1). The cell has a formula behind it: =IF(COUNTIF($T$2:$T$1618,"Implemented-N"),"No","Yes") This formula reads a range of cells, which have a formula behind them (Column T). Column T matches and puts together the data in 2 other cells along the same row: =D2&"-"&N2 When a cell in Column T turns to "Implemented-N" it returns the value of "No" to Cell Q1. When cell Q1 changes to "No" I would like my macro that calls up an email to be activated. I have the code for the email but can't get it to activate when cell Q1 changes to "No". Can anyone please help me?

You will need to place the check and the call in a Worksheet_Calculate Event. Or bypass the formula and use a Worksheet_change event that searches the column for the desired criteria every time the data in that range changes. — Scott Craner

Xabier Xabier · Accepted Answer · 2018-01-02T17:50:25

As Scott mentioned in the comments something like the following would do the trick:

Private Sub Worksheet_Calculate()
    If Range("Q1").Value = "No" Then Call YourMacro 
'if the value of Q1 changes to No then call your macro/code
End Sub

Active macro when a formula changes a cell to True

1 Answers