Looking for changes in data via comparison

[excelspud]

Hi,

I am looking for the correct VBA coding to be able to compare if a change has been made to my worksheet. I need to be able to alert the user, when a particular macro is run, if any changes are evident. For example, I have listed a number of companies with associated values and I need to run a comparison when I run the macro with name (eg) CompanyAccounts. The coding just needs to be able to check whether any changes have been made to the companies or values, and alert the user if it has, when I run CompanyAccounts macro.

Any help would be much appreciated

[foxguy]

excelspud;

VBA can only compare 2 values. It has no way of looking up what the old value used to be. So you need to decide how and where to store the old value BEFORE any one changes it.

You could make a copy of the worksheet, then when the macro is runs it compares the current sheet with the copied sheet.

Or you could have the workbook just make a note of what cells were ever changed and list the changes when the macro is run (it won't show the old value, just the address of changed values). Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r as Range
    With Sheets("Changes")
        Set r = .Range("A" & .Rows.Count).End(xlUp).Offset(1)
        With r
            .Value = Now
            .Offset(0,1).Value = Target.Address
        End With
    End With
End Sub

Then your macro would do something like this:

Sub ListChangeValues()
    Dim r as Range
    Dim s as String
    Dim vbAns As VbMsgBoxResult
    Dim rLastRun as Range

    With Sheets("Changes")
        Set rLastRun = .Range("A1")
        Set r = .Range("A2")
        Do While r.Value > rLastRun.Value
            s = s & vbcr & r.Offset(0,1).Value            
            Set r = r.Offset(1)
        Loop
        If s = "" Then
            MsgBox "No Changes Since " & rLastRun.Value
        Else
            vbAns = MsgBox("Changes Made After " & rLastRun.Value _
                    & vbcr & "Do You Want to Update Run Date? _
                    & s, vbYesNo)
            If vbAns = vbYes Then
                rLastRun.Value = Now
            End If
        End If
    End With
End Sub