[Solved] Comparing before and after cell value change

    Hi everyone,


    I've been searching in vain for a means of comparing a column of values after their precedent cell has been changed with their values before the change.


    To be specific, all formulae in col. AE refer to the value in D1. If D1 is changed, how can I establish which displayed values in col. AE have changed as a result?


    It would be simple enough to load col. AE's values into an array with a Worksheet_activate event, but the values would be dumped as soon as the event procedure terminated. What's needed is a _BeforeCalculate event, but such an animal does not exist.


    All ingenious suggestions gratefully appreciated.


    Richard Fuller

    Did you try Tools->Audit? Trace dependents for the cell which is changing in col. D?


    This will not require any coding, still will give you the thing you need (i suppose!!)


    Post back if this is not acceptable.

    Thanks: ~Yogendra

    Hi Yogendra,


    Won't do, I'm afraid - once the changed values have been identified I need to run some code on them. Thanks anyway!


    Richard

    I can offer a work around I have used to a similar problem that may or may not be suitable for your application.


    Works by setting calculation to manual and picking up values to check in sheetchange event then calculating the sheet and checking for changes in sheet calculate event.


    All this code is placed in the sheet concerned.


    Option Explicit
    Option Base 1
    Dim ColCheck(20)
    Dim Count As Long


    Private Sub Worksheet_Activate()
    Application.Calculation = xlCalculationManual
    For Count = 1 To 20 'Load Array with current values
    ColCheck(Count) = Cells(Count, 31) 'Column AE values
    Next
    End Sub


    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = "$D$1" Then 'if D1 is changed
    For Count = 1 To 20 'Load Array with old values
    ColCheck(Count) = Cells(Count, 31) 'Column AE values
    Next
    End If
    ActiveSheet.Calculate


    End Sub


    Private Sub Worksheet_Calculate()
    For Count = 1 To 20
    If ColCheck(Count) <> Cells(Count, 31) Then
    MsgBox "Column AE Row " & Count & "has changed" & _
    "Previous value " & ColCheck(Count) & _
    " New value " & Cells(Count, 31).Value
    End If
    Next


    ' run some code based on changes


    For Count = 1 To 20 'Load Array with new values
    ColCheck(Count) = Cells(Count, 31) 'Column AE values
    Next



    End Sub


    Private Sub Worksheet_Deactivate()
    Application.Calculation = xlCalculationAutomatic
    End Sub


    This code will compare rows 1-20 of column AE and tell you of changes if D1 is altered.


    Hope it is of some use, Insomniac

    Insomniac,


    Very cunning - setting calculation to manual is the idea I'm looking for. Many thanks indeed for your help.


    Richard Fuller

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login