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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Formatting to show cell value has changed
- Thread starter inarbeth
- Start date
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.)
(You'll also need a reset button that either clears all formatting or rereads the audit trail and clears formatting on featured cells.)
Upvote
0
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
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
Upvote
0
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 .
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 .
Upvote
0
