Running an Excel macro on cell change in a range of cells, the cell contains a formulae

[Phil-365]

I am trying to run a macro when a cell in a range of cells changes, all the cells in the range contain Formulae

[#2]

The change event of a worksheet is only triggered by editing a cell, not if the result of a cell's formula changes.

[#3]

Thanks for your reply, Is there any way of responding to an Excel Cell that has been changed @Jan Karel Pieterse 

[#4]

Can you post a sample workbook? (anonymized)

[#5]

Sorry for my ignorance but how would i anonymize the sample workbook@Jan Karel Pieterse 

[#6]

By making sure there is no sensitive data in the workbook like personal details, company details and etcetera. Best to just create some nonsense data that does enable us to answer your question. Make sure you include expected outcomes as well.

[#7]

Hi Jan

Thanks for your time

What I am trying to achieve is to establish if a cell in the range D2:D10 changes when the formulae is true and so shows Go. I hope that makes sense

@Jan Karel Pieterse 

[#8 · Accepted solution]

@Phil-365 You could do it like so:

Option Explicit

Dim PrevData As Variant

Private Sub Worksheet_Calculate()
    Dim Rw As Long
    Dim curData As Variant
    If IsEmpty(PrevData) Then
        PrevData = Range("D2:D10").Value2
    End If
    curData = Range("D2:D10").Value2
    For Rw = LBound(curData, 1) To UBound(curData, 1)
        If curData(Rw, 1) <> PrevData(Rw, 1) Then
            MsgBox "Row " & Rw + 1 & " has changed!"
        End If
    Next
    PrevData = Range("D2:D10").Value2
End Sub

[#9]

Hi Thanks Jan

I will place it in the required program which will make an immense difference to what we are tring to achieve. I have tried it in the example Workbook which obviously works, it will take out having to test the whole of the Cell range as your code supplies the Row. Once again Thanks for your time.

@Jan Karel Pieterse