how to check which cells are recently updated

skgurmeet

New Member
Joined
Oct 31, 2010
Messages
10
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Here is some code that could be used to generate the baseline worksheets.

Code:
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. :eek: 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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top