Tracking Changes in Excel

Microsoft Office excel 2007 - upgrade
July 6, 2010 at 10:49:46
Specs: Windows XP

I'm trying to track changes made in an excel spreadsheet. I have several formulas in the spreadsheet which I don't want to protect because the individual user may need to remove the formula and modify the cell contents manually. I'd like to be able to highlight the cell if the user deletes the formula and manually enters a value so it is easier for management review. I tried working with the conditional formatting but I can't find a way to create a rule for it. I'm thinking it may require some kind of macro but I'm really not proficient in macros. Any help would be greatly appreciated.


See More: Tracking Changes in Excel

July 6, 2010 at 12:30:01

How about an approach that allows users to replace a calculated value with a user-entered value without removing the formula.

Try this:
In cell E1 enter 2
In cell E2 enter 4

In cell E3 is your formula: =E1*E2 - your formula calculated value is 8).
Use cell D3 for the user entered data.
(You can add highlighting etc. to D3 to identify it as a user-permitted cell)
Now replace the formula in E3 with this:
Add conditional formatting to cell E3
Use the Formula Is: option and this formula:
Note that TRUE and FALSE are not in double quotes - they are the Excel logical values true and false.
Select a Red format for the cell and hit OK.

Now if a user wants to enter a value to replace a formula calculated value they can do so.
The formula is not lost
and the fact that a User-entered value has been used is clearly visible.

Leaving cell D3 empty and you get 8 in E3
Enter 10 in cell D3 and you get 10 in E3 and its flagged in Red.


Report •

July 7, 2010 at 04:37:09
Thanks for the input. Unfortunately it's really not the best solution for my project. The spreadsheet needs to be able to "grow" and the end users of the spreadsheet are not proficient in excel enough to update it as needed. I really was looking for some type of conditional format formula that I could blanket over the entire worksheet but I think I may have to add a "personalized" conditional format in each cell that would say to highlight the cell if it does not equal the results of the formula. Not sure if it will work the way I need it to but I think that is my best bet.

If you have any other suggestions I'm always open for them.

Thanks again!

Report •

July 7, 2010 at 05:24:39
Actually, I think what I may need is an if statement that would allow me to indicate if a formula is used or a constant numeric value. I could put it into a hidden column next to the data I need to test and add conditional formatting to the column that is showing to say that if the hidden cell indicates that there is a constant value then highlight the cell that is showing. I just don't know how to do a formula that looks for a formula. Cell(type) does not do what I need it to do. Any ideas?

Report •

Related Solutions

July 7, 2010 at 05:29:42
I have never used it, but there is a Track Changes function built into Excel:

On the Task Bar/ Ribbon
Review Tab
On the far right side, Track Changes

Look here for pointers on it's use:


Report •

July 7, 2010 at 05:50:16

If you really want to go the route you are proposing - it seems unduly complicated and just as problematic from a 'growing' point of view - you could use the following user defined formula which will return TRUE or FALSE depending on whether the cell referenced contains a formula:

Enter this in a standard VBA module:

Public Function ISFORMULA(TestCell As Range)
If TestCell.HasFormula = True Then
    ISFORMULA = True
    ISFORMULA = False
End If
End Function

Use this:

in any cell.
In this example if B8 contains a formula, the cell will return the logical value true, which can then be used in a conditional format.

Apply this 'Formula Is:' conditional format to cell B8:
If there is a value rather than a formula in B8, the conditional format will be applied (assuming C8 contains the user defined function).


Report •

July 7, 2010 at 08:55:10
Thanks for the help. I wound up going with something that is very complicated and will likely need to be updated if it is larger than I have allowed for but I have not worked with VB in a long, long time and have no idea how to do anything in 2007 with it so I opted out for now and will try to get into a class to learn it because I know it really will make my life easier.

Thanks again.

Report •

July 7, 2010 at 10:24:04

You don't need to understand a lot about VBA to use a pre-written user defined function (UDF).

Here is a step-by-step guide for Excel 2007:

As it sounds like you are distributing the workbook, you will need the UDF to stay with the workbook.

Save the workbook in a macro-enabled format. *.xlsm

Right-click the name tab at the bottom of the worksheet and select 'View Code'
This opens the Visual Basic window, with your worksheet name selected in the Project Explorer pane.
Right-click the worksheet name and select 'Insert' and 'Module' (not Class Module).
In the large visual basic window (the one with two drop-downs at the top), paste in this code.

Public Function ISFORMULA(TestCell As Range)
If TestCell.HasFormula = True Then
    ISFORMULA = True
    ISFORMULA = False
End If
End Function

Click Save from the VB menu bar

Now use Alt + f11 (the Alt key and function key #11 together) to return to the main Excel window.

In an empty cell enter a formula like this:


The cell will return True or False as described in my previous post.



Report •

Ask Question