customize excel 2010 highlight track changes

Question

Is there any way to customize how Excel highlights cells on Track Changes? My customer requested us to "highlight anything you've changed by changing text color to red," and I'd like to implement this. I'm currently looking for a way to do it with VBA, and my thought is:

if (current cell has changed) and (changed has NOT been approved) then
    set color of current cell = red
else 
    remove color of current cell. 
end if  

Any suggestions are appreciated!

Answer 1

You can do this with a Worksheet Change event in VBA. I started building a simple example for you, but I realized your customer will probably want to be able to roll back any highlighting after they've checked your changes. So, I decided to make a whole working model that does everything. Here are the steps you'll need to follow:

Press Alt+F11 to open the VBA editor in Excel. In the Object Browser (On the left side of the VBA window), double click a sheet you will be editing. Paste the following code into the text field that opens.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet, ws2 As Worksheet
Dim i As Boolean
Application.ScreenUpdating = False

'Create Change Log if one does not exist.
i = False
For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "Change Log" Then
        i = True
        Exit For
    End If
Next ws
If Not i Then
   Set ws2 = ThisWorkbook.Worksheets.Add
   ws2.Visible = xlSheetHidden
   ws2.Name = "Change Log"
   ws2.Range("A1") = "Sheet"
   ws2.Range("B1") = "Range"
   ws2.Range("C1") = "Old Text Color"
Else
   Set ws2 = Sheets("Change Log")
End If

'Store previous color data in change log for rollback.
ws2.Range("A1").Offset(ws2.UsedRange.Rows.Count, 0) = Target.Worksheet.Name
ws2.Range("B1").Offset(ws2.UsedRange.Rows.Count - 1, 0) = Target.Address
ws2.Range("C1").Offset(ws2.UsedRange.Rows.Count - 1, 0) = Target.Font.Color

'Change font color to red.
Target.Font.Color = 255

Application.ScreenUpdating = True
End Sub

This code will change the font color to red of any cell you edit the value. It will not change the font color if you only change the format of a cell. Also, it will not automatically change the font color of dependent cells. The latter could be done, but I'll leave that to you if you want that behavior.

This code also creates a hidden Change Log sheet and records the address and original font color of the cell that has been changed.

Note that this code only works for changes in that specific sheet. If you want to track changes in the entire workbook, you can paste this same code in the worksheet module for each sheet.

The code to rollback any highlighting must be placed in a separate module. In the VBA editor, go to Insert >> Module. Paste the following code into the new module.

Sub rollbackHILITE()

Dim sht As Worksheet, cl As Worksheet
Dim j As Long, roll() As Variant
Dim del As Integer
Application.ScreenUpdating = False

'Find Change Log.  If it doesn't exist, user is prompted and exits sub.
For Each sht In ThisWorkbook.Worksheets
    If sht.Name = "Change Log" Then
        Set cl = sht
        Exit For
    End If
Next sht
If cl Is Nothing Then
    MsgBox "Change Log not found!"
    Exit Sub
End If

'Return font colors to original form by stepping backward through change log.
If cl.UsedRange.Rows.Count > 1 Then
    roll = cl.Range("A2:C2").Resize(cl.UsedRange.Rows.Count - 1, 3)
    For j = UBound(roll, 1) To 1 Step -1
        Set sht = Sheets(roll(j, 1))
        sht.Range(roll(j, 2)).Font.Color = roll(j, 3)
    Next j
End If
Application.ScreenUpdating = True

'Prompt User to keep or delete change log after rollback.
del = MsgBox("Delete Change Log?", vbOKCancel, "Finish Rollback")
If del = 1 Then
    cl.Delete
End If

End Sub

This code can be run from the Macros menu in Excel. It simply reverts the font color of highlighted cells back to their original colors and then deletes the change log if the user OKs it.