Mark "track changes" in MS excel

[navis]

Hi folks,

I am stuck with this topic so maybe you can help me.
We sent out a template with data that have to be updated by our
employees. We enabled "Track changes" (shared document) to verify what
data was changed.

So far so good. No I want to mark the cells that have changed, for
example set the background color to red, and unshare the document
(history will be lost).

I have written a macro that marks a cells which have comments in it but
how can I check if the cell was changed in the past?

Thx in advance for your help.

Christian

P.S. the code to find a comment in a cell

##### snipet #####
Public Sub FindComments()

Dim rngToSearch As Excel.Range
Dim rngToFind As Excel.Range

On Error GoTo ExitFindComments

'Set the range to check for comments
Set rngToSearch = Selection

For Each rngToFind In rngToSearch
If Not rngToFind.Comment Is Nothing Then
rngToFind.Interior.ColorIndex = 3

End If
Next rngToFind

ExitFindComments:

End Sub

 

[Bernie Deitrick]

Christian,

I think you just use the Tools / Track Changes / Highlight Changes....

Or in code:

With ActiveWorkbook
.HighlightChangesOptions When:=xlAllChanges, Who:="Everyone"
.HighlightChangesOnScreen = True
End With

Note that hgihlighting cells with comments won't show the cell that have change notices attached.

And your code below could be better:

Public Sub FindComments()
On Error Resume Next
Selection.SpecialCells(xlCellTypeComments).Interior.ColorIndex = 3
End Sub

HTH,
Bernie
MS Excel MVP