change

[RKS]

Hi
I have a excel file which we can send to user. how we can know if user
change any value in my sheet. i can allow to user to change the value. my
purpose is that we would know which cell user change value.

if it is possible if user change any value that cell will be red. please
help me.

Thanks
RKS

 

[Gary''s Student]

Put the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Application.EnableEvents = False
t.Interior.ColorIndex = 3
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Dave Peterson]

Changing the colorindex doesn't cause the worksheet_change event to fire.

You could just use:

Private Sub Worksheet_Change(ByVal Target As Range)
target.Interior.ColorIndex = 3
End Sub

 

[Dave Peterson]

Just to add to GS's response:

This kind of thing depends on the cooperation of that end user.

If the end user makes a non-change change (reentering RKS as RKS, for instance),
the fill color will change.

If the end user disables macros or even events, then you won't see the changes.
And the end user can just change the fill color back.

Depending on what you're allowing the users to do (change cells, but no
inserting/deleting rows or columns???), you may want to keep a copy of the
worksheet (worksheets???) and use another program to make the comparison.

http://www.cpearson.com/excel/whatsnew.aspx
look for compare.xla

It does that cell by cell comparison (A1 with A1, x99 with x99, ...)

So inserting a new row 1 will pretty much make the results worthless.