Indicate when data in a cell is changed (Excel 2000)

Topic

New Reply

I’m using the code below to color a cell if the data changes. It works good.
The problem is I have an option group in a column on the sheet and id doesn’t
change the background when you select a different option. The cell that is linked
works because I see the numbers change. Is there something else I need to make it work for the option buttons.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2", "M154")) Is Nothing Then
        With Target.Interior
            .ColorIndex = 6
            .Pattern = xlSolid
        End With
    End If
End Sub

Scott

Reply | Quote

Replies

The Worksheet_Change event only occurs when the user changes the value of a cell directly, not when the value changes as the result of something else such as a calculation, a macro, or because it is linked to a control. You could assign a macro to each of the option buttons – not a very attractive idea… Do you really need the cells to change color when the user clicks an option button?

Reply | Quote

Hans,
Yes I do. Unless you can think of another way to show that a value has been changed.
The working sheet will have around 150 rows.
I want an easy way to let the sender of the sheet know when a user makes a change and sends it back.
There may only be a couple of changes on the sheet and it would be hard to compare the changed sheet with the original sent.
If I change an option button and try to close, I’m asked if I want to save the changes so something is seeing that I changed data.
Is there a way to use the Calculate event?

Scott

Scott.

Reply | Quote

The Excel application itself keeps track of a lot more than is available to the programmer through events.
The Worksheet_Calculate event doesn’t have a Target argument, so you don’t know which cells were affected.
A disadvantage of code is that a cell will remain colored even if the user undoes changes and restores the original value.

Perhaps you can adapt the attached version to your needs. I copied the linked cells (C2:C11) to the clipboard, then used Paste Special > Values to place their current values in G2:G11.
I set conditional formatting on C2:C11 to color the background if the value of a cell is not equal to its counterpart in G2:G11.
Finally, I hid column G.
If the user clicks an option button, the cell underneath will be colored. If the user restores the original situation, the coloring will disappear.
No code needed, so the user will not get a macro warning.
A disadvantage of this method is that you must prepare the worksheet before sending it out.

Reply | Quote

Hans,
Thanks. That helped me finish the sheet.
Works great.
Scott

Reply | Quote