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. [Ok]

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

    Err.Clear
    On Error Resume Next
    Worksheets("SaveHistory").Activate
    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"
            [A2].Activate
        End With
    Else
        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
    Sheets(currentsheet).Select
    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
  Sheets(Target.Value).Select
End Sub

A simple macro to mark cells that are changed, uses Worksheet_Change -- also has a version to add update information to a cell comment.
Highlight *all* changed cells after entry? posted by Christian Jones and solved by Jake Marx this is about the middle of the thread but has the solution. Poster wanted the ability to color cell that is changed and all the cells that were actually changed as a result. In other words an IF might not actually change the value in a cell. Uses Sub Worksheet_Calculate() to do this.
MS KB Q130560, XL: Commands and Features Unavailable in Shared Workbook (Q130560)
List of Current Users with VBA, Bill Manville, show who has Shared and who has Exclusive. ActiveWorkbook.UserStatus

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

Highlight, Change Highlighting

Warning Shared library and can't get it unshared

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

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

Related (#related)