Forum Discussion

Phil-365

Copper Contributor

Feb 25, 2020

Solved

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

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

Macros and VBA

JKPieterse

Feb 25, 2020

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

Phil-365

Copper Contributor

Feb 25, 2020

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

JKPieterse

Cell Change .xlsm17 KB

JKPieterse

Silver Contributor

Feb 25, 2020

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

Phil-365
Copper Contributor
Feb 25, 2020
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.
JKPieterse