how to check which cells are recently updated

[skgurmeet]

Hi,

I have a workbook which keeps on updating cell values based on changes of data in external links. this workbook updates values on hourly basis. most of time only few cells change the values and others remain same, but i find it difficult to find which are the cells whose values are changed during last update. Is there any way to find cells which have changed values after latest update from external links, and is it possible to automatically change the color of cells which are recently updated, whereas the cells which have same values after update remain in automatic color.

Thanks,
Gurmeet

 

[Jerry Sullivan]

Hello,

I'm not aware of any simple built-in way to identify changed cells. One option would be to copy and paste the data as Values into a different worksheet in your workbook. This would act as your "baseline" record as of your last update.

Then use conditional formatting to compare your "current" worksheet (with the links) to the baseline with the saved values. Have the conditional formatting highlight any cells in the current worksheet whose values are different from the corresponding cell in the baseline worksheet.

Hope this helps.

 

[skgurmeet]

hi dear, thanx for replying.

indirect methods are there, but i have multiple worksheets which keep on updating.. so it would be difficult to manually do this by copying values in other sheet and compare after update. some automatic in-built or customized function would only serve the purpose.

regards,
gurmeet

 

[Jerry Sullivan]

Doing this with a macro would be fairly simple.

Each one of your "current" worksheets would have a corresponding "baseline" worksheet.

Each current worksheet would have the conditional formatting (CF) comparing to the baseline.

You would just need to decide a few details....what event would trigger the copying of the current worksheets to set new baselines (macro button, everytime file is closed?)

Which of the parts would you like help setting up?

 

[skgurmeet]

can u plz post an example for how to do it.

i still believe there must be some easier way to do it instead of making base worksheets for all volatile sheets and the keep on comparing them.

 

[Jerry Sullivan]

Here is some code that could be used to generate the baseline worksheets.

Sub Update_Baselines()
    Dim ws As Worksheet
    Dim lngIndex As Long
    Application.ScreenUpdating = False
    For lngIndex = 1 To ActiveWorkbook.Worksheets.Count
        With Worksheets(lngIndex)
            If Left(.Name, 8) <> "Baseline" Then
                Setup_Sheet ("Baseline_" & .Name)
                .UsedRange.Copy
                Worksheets("Baseline_" & .Name).Range("A1") _
                    .PasteSpecial (xlPasteValues)
            End If
        End With
    Next
    Set ws = Nothing
End Sub
 
Private Function Setup_Sheet(strName As String)
    On Error Resume Next
    If (IsError(Sheets(strName).Activate)) Then
        Worksheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = strName
    Else
        Sheets(strName).Cells.Clear
    End If
End Function

I was mistaken on the approach of using CF to compare volatile worksheets to the baseline. CF doesn't allow you to use references to other worksheets or workbooks.

i still believe there must be some easier way to do it instead of making base worksheets for all volatile sheets and the keep on comparing them.

Maybe...I don't know what that would be. Particularly difficult if your workbook is closed when the linked sheets are being updated.

If you want to go with an approach like the baseline...two workarounds to the CF limitation would be:
1. Copy data elsewhere on the same worksheet. (To Right of Used Range)
2. Use VBA to compare volatile sheet to baseline sheet and highlight differences.

 

[skgurmeet]

Hi Jerry,

don't know about earlier versions of excel, but in excel 2010 I've checked the conditional formatting works when you compare two different worksheets.

 

[Jerry Sullivan]

Oh...that's a nice improvement.

Sorry I don't know an easier way to check for updated cells.

BTW, I'm assuming that you are only updating values and not adding/removing rows.
The solution I suggested wouldn't be much help in that scenario since everything below an added row would be considered "changed".

Good luck...I will be interested to learn if you or someone else comes up with a better way.

 

[skgurmeet]

yes, only values are updated, and rows columns are not added or removed.