Formatting to show cell value has changed

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912
Is it possible to conditionally format a worksheet to show any cells that have changed in the last X days? I do not wnat to have to save a version of the sheet and then compare it with the latest version but rather to be able to see what changes have been made over different periods, eg the last 7 or 10 days. I should also like to be able to reset the sheet to remove the new conditional formatting whilst leaving any pre-existing conditional formatting in place.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

dmckinney

Board Regular
Joined
Jul 10, 2002
Messages
120
possible approach is to have an audit trail. On the change event of the worksheet record the range that has changed in a [hidden] worksheet (range.address), and the date. Then write a function to read the audit trail and highlight cells changed after a certain date. I started to write the code for this but I managed to crash Excel with it - so tread carefully!

(You'll also need a reset button that either clears all formatting or rereads the audit trail and clears formatting on featured cells.)
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Place this code in the WorkSheet module. It will add a comment to a cell whenever its value is changed.

Public acVal
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
Target.AddComment
Target.Comment.Text "The previous entry was " & acVal & " changed on " & Now()
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveCell.Address<> Target.Address Then Exit Sub
If Target.Value = "" Then
acVal = ""
Else
acVal = Target.Value
End If

End Sub
This message was edited by lenze on 2002-08-29 13:54
 

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
Excel has an automatted method:

The concept is the same as what lenze provided, but it shows you what it was changed from. This will not allow macros.


Highlight changes as you work
When you mark changes in a workbook by using the Highlight Changes command, you also turn on workbook sharing (if the workbook is not currently shared) and the change history. For information about changing the change history settings, click .

Some Microsoft Excel commands and features are not available in a shared workbook. For more information, click .

On the Tools menu, point to Track Changes, and then click Highlight Changes.
Show Me

Make sure the Track changes while editing check box is selected. This check box turns on workbook sharing and the change history.


Select the When check box, click Not yet reviewed in the When box, and then click OK.


If prompted, save the workbook.


Make the changes you want on the worksheet. Microsoft Excel marks cells that you change, insert, or delete with a highlight color.
Note When highlighting changes, Microsoft Excel does not mark some changes, such as formatting. For more information about which changes are marked and ignored, click .
 

Forum statistics

Threads
1,147,507
Messages
5,741,566
Members
423,667
Latest member
Kai_357

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top