automatically highlight modified cells

[arnm]

Version Excel 2007


We distribute a report to many users. The users edit some of the cells and return the report.

Question: How can we have the modified cells automatically highlight when edits are made?

We want to be able to view all edited cells at a glance.

Thank you for your help....!

 

[lenze]

You can use code, but you must make sure the users enable macros.
For a specific sheet, place this in the sheet module (RightClick the sheet tab and choose "View Code")

Private Sub WorkSheet_Change(ByVal Target as Range)
Target.Interior.ColorIndex = 6
End Sub

This is, of course very simplified.
1), You can not prevent them from undoing the Coloring
2) You can not see if they change the cell more than once.
3) This will color ANY cell changed in that sheet. It can be modified to specific cells.

To work on all sheets in the WorkBook, use this in the ThisWorkBook module

Private Sub WorkBook_Change(byVal Target as Range)
Target.Interior.ColorIndex = 6
End Sub

You can also use a comment
See my article<a href="http://www.vbaexpress.com/kb/getarticle.php?kb_id=909">Tracking Changes with Comments</a> at VbaExpress

lenze

 

[jpgreenlee]

Is there a way to highlight changes in this way but only for a week at a time? In other words, we update a spreadsheet and email it each week with the changes for that week highlighted and the next week, we want the new changes highlighted but not the ones from the previous week.

Also, I get a Compile Error: Ambiguous name detected: WorkSheet_Change when adding this code to my spreadsheet which has an auto-sort feature already.