Highlight, Change Highlighting

Location:   http://www.mvps.org/dmcritchie/excel/highlite.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

Excel has a facility to record changes to individual cells and places a black triangular mark in the upper left corner, similar to cell comments which uses a red triangle. If you changed content and had a cell comment, you will see both when you hover over the cell to see cell comments. (below)

If Highlight changes is in effect, your workbook is shared, and you will see “[Shared]” on the Title bar.

//See warning about not being able to access macros, format options grayed out//
Help topic:  Remove a workbook from shared use
Tools --> Share Workbook --> Editing (tab) --> uncheck Allow changes by more than one person --> OK --> Yes

You may use it alone or with the simple macro to mark cells changed.

Turning off the option turns a rather verbose dialog box (below).

To invoke/remove use Highlight Changes ( pink sheet with pencil)
That button is found during menu customize (view, tools, customize)
    Tools, move buttom to toolbar

For Excel 2007 try "Review" tab, and "Changes" box. "Track Changes" is in the lower right

Click on Highlight Changes button
check each of these on
    track changes, when: all, who: everyone, highlight change on screen

make some changes, save

click on Highlight Changes button
    now check the List changes on a new sheet
    (generates History sheet at end of worksheet tabs)

The first time I did the last one, 1/3 of my toolbar turned blank (white) as well as all the menus area. I recycled Excel and okay, but perhaps could have waved another window over it to repaint (??).

HELP (F1) --> answer wizard --> highlight changes Topic: Troubleshoot change highlighting and the History worksheet

something new to try, look at the messages below, you will not be able to change macros while this is in effect. When you turn option off it will have to start all over again. So you don't want to keep your macros in same workbook if want to change them.

example of the cell comment/change tooltip


David McRitchie,  06/17/2000 10:07PM
changed cell D23 from 'dd' to 'ee'

[cell comment]
David McRitchie:
This is a comment.

This action will remove the workbook from shared use. The change history will be erased, and the other users who are editing this workbook will not be able to save their changes, even if you share this workbook again.

Remove the workbook from shared use?

  • To make the workbook exclusive, click Yes.
  • To cancel and return to shared mode, click No.
    [Yes] [No]

This workbook contains macros recorded or written in Visual Basic. Macros cannot be viewed or edited in shared workbooks.

Warning Shared library and can't get it unshared

Microsoft Excel Vlookup.xls [Shared]
Change History

Since having this problem have learned about:
      Tools --> Share Workbook --> uncheck option

User controlled Save History as an alternative to Highlight Changes (#BeforeSave)

The following could be added to ThisWorkBook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)  
 '--Sub auto_close()     'Changed from Auto_Close to ThisWorkBook
 '--   If ThisWorkbook.Saved = True Then Exit Sub 
 '--   'since auto_close would miss if saved before closed 
    Dim currentsheet As String, currentaddress As String
    currentsheet = ActiveSheet.Name
    currentaddress = ActiveCell.Address(1, 1)
    Application.EnableEvents = False

    On Error Resume Next
    If Err.Number <> 0 Then
        With ActiveWorkbook
          .Worksheets.Add(after:= _
             .Sheets(.Sheets.Count)).Name = "SaveHistory"
            [a1] = "LastSaved"
            [b1] = "Active when saved"
            [c1] = "active Cell"
            [d1] = "UserName"
        End With
        Range("A1").End(xlDown).Offset(1, 0).Activate
    End If
    On Error GoTo 0
    ActiveCell.Offset(0, 0) = Now
    ActiveCell.Offset(0, 1) = currentsheet
    ActiveCell.Offset(0, 2) = currentaddress
    ActiveCell.Offset(0, 3) = Application.UserName
    If ActiveCell.Row < 3 Then Cells.EntireColumn.AutoFit
    Application.EnableEvents = True
End Sub
Something similar can be done if just interested in who opened the workbook see posting by Sandy V. 

Event macro to jump to a page from your SaveHistory Sheet (#jump)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True   'Get out of edit mode 
  on error resume next   'skip if not found
End Sub

You are one of many distinguished visitors who have visited my site here or in a previous location  since this page was created on June 17, 2000.

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved