Detect change in cell value caused by recalculation of changes made elsewhere

[Kelvin Stott]

Hi,

I would like to run a macro if any cells in a given range change in value. I tried to use Private Sub Worksheet_Change(ByVal Target As Range), however this only seems to return the cells whose values are changed directly (i.e., as input values), rather than by recalculating formulae that are based on inputs elsewhere in the workbook.

Is there any way to identify all cells that are automatically updated in a sheet as a result of recalculation, as well as any cells which are changed manually?

Thanks for any help.

Kelvin

 

[NoSparks]

This doesn't answer the exact question you've asked but may address the thread title.

Any cell changing via direct entry (or a macro writing to it) can trigger the WorkSheet_Change event.
Knowing which cell triggered it may not be a necessity.
If you have a copy of your 'given range' stored somewhere as values only, no matter what triggers the WorkSheet_Change sub you can have instructions in it to compare the stored values with the current 'given range' values, thus knowing if something in the 'given range' changed.
Hui's response here may be of interest.

 

[Jaafar Tribak]

You could try something along these lines :

Code goes in the ThisWorkbook Module:

Option Explicit

Private Const RANGE_ADDRSS = "A1:D20"     [B][COLOR=#008000]'<==Change range address as required.[/COLOR][/B]
Private Const SHEET = "Sheet1"            [COLOR=#008000][B]'<==Change Sheet name as required.[/B][/COLOR]


Private Sub Workbook_Open()

    Dim oCell As Range
    
    For Each oCell In Sheets(SHEET).Range(RANGE_ADDRSS).Cells
        If oCell.HasFormula Then
            oCell.ID = oCell.Value
        End If
    Next

End Sub


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

    Dim oCell As Range, sMsg As String, bValueChanged As Boolean
    
    sMsg = "Cells calculated in range: " & Sheets(SHEET).Range(RANGE_ADDRSS).Address(, , , True) & vbNewLine
    sMsg = sMsg & "==============================================" & vbNewLine & vbNewLine
    sMsg = sMsg & "Cell" & vbTab & "Old Vlaue" & vbTab & "New Value" & vbNewLine
    
    If Sh Is Sheets(SHEET) Then
        For Each oCell In Sheets(SHEET).Range(RANGE_ADDRSS).Cells
            If oCell.HasFormula Then
                If oCell.Value <> oCell.ID Then
                    bValueChanged = True
                    sMsg = sMsg & oCell.Address & vbTab & IIf(oCell.ID = "", Space(30), oCell.ID) & vbTab & oCell.Value
                    sMsg = sMsg & vbNewLine
                    oCell.ID = oCell.Value
                End If
            End If
        Next
        If bValueChanged Then MsgBox sMsg
    End If

End Sub