Call Macro dynamically when cell value changed by formula

Question

In Excel, through "Data connection", I am getting live data in cell A1. Using A1's value, I am doing some calculation on cell B1. If the value in B1 is TRUE, then that value should be copied to C1.

I have so far been unable to achieve the desired result and would appreciate some guidance.

Answer 1

If you are refreshing cell A1 externally and want to perform the copy based on cell B1's calculation, then you can use the Calculate event macro.

Say we want the first value in A1 that exceeds 100 to be captured in C1. Once the first value has been captured, no other values above 100 will be copied.

In B1 we enter:

=AND(A1>100,C1="")

and then install this event macro in the worksheet code area:

Private Sub Worksheet_Calculate()
   Dim B As Boolean
   B = Range("B1").Value
   If B Then
      Application.EnableEvents = False
         Range("C1").Value = Range("A1").Value
      Application.EnableEvents = True
   End If
End Sub