Tracking Changes

Microsoft Microsoft excel 2007 open lice...
February 23, 2010 at 12:35:43
Specs: Windows XP
In Excel 2007, I want to highlight an individual cell in a given row if a change is made to any of the cells in that row.

I am aware of the Track Changes function in Excel, but I do not want to go through each individual change to accept or reject. I just want a quick visual notification that a change has been made.

See More: Tracking Changes

Report •

February 23, 2010 at 13:05:03

When would the 'highlight' get removed?

I think it would be possible to have a macro that is triggered whenever a cell in the row is changed and the indicator cell could be changed, say to red on a change, then green on the next change, then back to red on the next change & so on.

Alternatively the highlight could be removed when the workbook is saved or closed.


Report •

February 24, 2010 at 06:25:12
Thanks, Humar.

The file is used by several individuals and they make changes and add information. I want to be able to identify the line items that have had changes to track status and activitiy. I would plan to 'unhighlight' (unfill) the indicator cell after I have reviewed the changes.

I am not very adept at creating macros or using Visual Basic so I was hoping maybe someone could point me towards a function in Excel or to a ready made macro that would just need a little customizing.

Report •

February 24, 2010 at 07:06:29
I would plan to 'unhighlight' (unfill) the indicator cell after I have reviewed the changes.

If the initial condition of the cell is always Blank
then there is a Conditional Formatting solution you could use:

In cell A1
On the Task Bar
Conditional Formatting

Under Condition 1 change Cell Value is to Formula Is

Add the formula:


Click the Format button
Select the Pattern Tab
Click No Color
Click OK

Next Click the Add button

Under Condition 2 change Cell Value is to Formula Is

Add the formula:


Click the Format button
Select the Pattern Tab
This time select an appropriate color
Click OK
Click OK

Now when any information is entered into cell A1 it will change to your selected color. If the cell is left blank, then it reverts back to no color.


Report •

Related Solutions

February 24, 2010 at 07:11:00
Just realized you using 2007.

I’m using Excel 2000 so these directions may be off a bit:

1) Select your cell, A1

2) On the ribbon click Conditional Formatting

3) Click on New Rules, it’s near the bottom of the dialog box.

4) Click Use Formula to determine which cells to format.

5) Enter the formula: =IF(A1="",TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select no color

9) Click OK

Repeat for Condition 2.


Report •

February 24, 2010 at 08:13:47

Here is a short macro that will place an "X" in column A on the same row as any change.

The weakness is that the user could delete the X after making a change. If this is a big issue, you could protect the cells in column A, and modify the macro to unprotect and reprotect the worksheet as required .... but this requires the worksheet password in the macro, which could be seen by a user in the visual basic window ... to go further you could put the macro in an Add-In and password protect that.

Anyway, here is the simple solution:
This code is added to the worksheet in question as follows:
Right click the worksheet's name tab and select 'View Code'
In the window that opens paste this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHnd

'stop further changes re-triggering this macro
Application.EnableEvents = False

'only respond to 'other users' changing cells in all columns except A
If Target.Column <> 1 And Application.UserName <> "John Doe" Then
    'enter "X" in the cell in column A, same row
    With ActiveSheet.Range("A" & Target.Row)
        .Value = "X"
        .Font.Color = RGB(255, 0, 0)
        .HorizontalAlignment = xlCenter
    End With
End If
Application.EnableEvents = True
Exit Sub

'error handler
Application.EnableEvents = True
End Sub

To stop the X's appearing when 'you' make changes, your User name is required in the code.
Change this line

If Target.Column <> 1 And Application.User <> "John Doe" Then

Click Save from the visual basic File menu.

Now when any user, except yourself, changes a cell, an "X" will appear in column A.


Report •

February 24, 2010 at 12:52:51
Thank you very much Humar! That macro does exactly what I want to do.

Also, thanks to Mike for the feedback. I will probably use that conditional format function as well.

Report •

Ask Question