Highlight cells with changed data on a worksheet with tracking changes

jpb103 · #1 06-02-2014, 12:04 PM

Greetings internet brothers and sisters,

I'm looking to write a macro that will highlight (or change the fill color) of cells that have changes (that have not yet been reviewed/accepted) on a worksheet with tracked changes enabled.

By default, tracking changes puts a tiny little comment looking triangle in the corner of cells that have been changed. Its not overly easy to see, at a glance, which cells have been modified. I'm aware that you can also export the change list to a 'History' worksheet, but my collegues don't much care for this view either. Any help would be greatly appreciated.

May bright moons guide your steps,

JP

jpb103 · #2 06-03-2014, 07:33 AM

Solved. Code follows:

Code:

'///////////////////////////////////////////////////////////
'///////WorkSheet_Change - Highlight changed cells//////////
'///////////////////////////////////////////////////////////
Private Sub Worksheet_Change(ByVal Target As Range)
'Set range
Const WS_RANGE As String = "A12:AG100"
'Error handler
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
    With Target
        'Set cell fill color to yellow
        .Interior.ColorIndex = 6
    End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
'//////////////////////END//////////////////////////////////
'///////////////////////////////////////////////////////////
'////////Clear_Changes -> Revert cell color to none/////////
'///////////////////////////////////////////////////////////
Sub Clear_Changes()
    'Set range
    Range("A12:AG100").Select
    With Selection.Interior
        'Set fill color to none for cell range
        .Pattern = xlNone
    End With
End Sub
'//////////////////////END//////////////////////////////////
'///////////////////////////////////////////////////////////
'////Clear_Selected_Cell -> Revert cell color to none//////
'///////////////////////////////////////////////////////////
Sub Clear_Selected_Cell()
    With Selection.Interior
        .Pattern = xlNone
    End With
End Sub
'//////////////////////END//////////////////////////////////

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Using macros to highlight worksheet words referenced in external list/doc.	Daniel_NYC	Word VBA	1	04-21-2014 03:35 PM
Can you copy & paste cells across worksheets and preserve reference to worksheet?	New Daddy	Excel	2	11-27-2013 07:19 AM
Cells no longer wrap text when changed in nested table	erik2000	Word Tables	1	03-29-2013 03:27 PM
"Auto-populating" data-worksheet to worksheet.	meggenm	Excel	4	02-04-2012 02:04 AM
Cells from other worksheets apear on current worksheet	Equilar	Excel	3	05-03-2010 01:40 AM