MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Identifying which row has been modified?

  Asked By: Darcy    Date: Mar 23    Category: MS Office    Views: 770

Can you folks tell me is this possible in a macro?
Suppose you have a excel sheet with many rows?

Last column suppose there is flag indicating whether it is 'Original'
or 'Updated' row.

In case user changes any data in the row, macro will get fired and it
will change the flag to 'Updated'.

Can you please tell me how this can be done?

This is I need for importing data from excel sheet. So if rows are
modified then only I will update that record in database? Now as I am
not able to know which row is modified by user, I have to update all
the rows.



2 Answers Found

Answer #1    Answered By: Willis Hill     Answered On: Mar 23

I think that the easiest way of doing this is to alter the
original database  itself to record  a timestamp on each record in each
table, identifying  the last time that record was updated  (best done
with a automated Trigger).

Once this has been achieved then a timestamp could also be included
in the XL identifying the same thing.

Then when it comes to updating, you only need to import records which
have been updated more recently than time X.

Answer #2    Answered By: James Williams     Answered On: Mar 23

I have designed a simple macro  to help you to identify all changes
that have done by another user  but you will need to follow certain
discipline. You need to duplicate your entire spread sheet  into
Sheet2 and to protect others for making changes in this Sheet2 you
have to password protect it and you will need to unprotect it
whenever you need to highlight changes in your original spread sheet
that has changes in it.

Sub Macro1()
' Macro created to highlight all changed values in an entire spread
' Macro created on 1/25/2006 by Mactikus
' Assign shortcut key Ctrl+Shift+C
RowNo = Cells.SpecialCells(xlCellTypeLastCell).Row
ColNo = Cells.SpecialCells(xlCellTypeLastCell).Column

For i = 1 To RowNo Step 1
For j = 1 To ColNo Step 1
Cells(i, j).Select
If Sheets("Sheet2").Cells(i, j).Value <> ActiveCell.Value Then
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
Next j
Next i
End Sub

Run this macro on the spread sheet that you wish to highlight all
changes. All changes will be highlighted in Red. You can choose your
other favorite color if you wish.

I just joined this group recently and hope this macro can still
comes in time. Enjoy using this macro.

Didn't find what you were looking for? Find more on Identifying which row has been modified? Or get search suggestion and latest updates.