excel - Need a macro to detect if cell value changes from current value

Question

I need help with an macro to notify me (by changing a cell background color to red), when the value (always number format) changes in any cells in the row. I want the background of cell E3 to change to red, if any of the values in cells F3:AN3 change from their current values.

The numbers in cells F3:AN3 will be entered manually or thru copy and paste of the row, and there won't be any formulas. Likewise, if any values in cells F4:AN4 are changed, I would like cell E4 to change to a red background, and so on for each of the rows in the chart. Not all rows will always have a value, so I would be looking for changes from "" to any #, or from one # to another #, or from any # to "". Ideally this would be an event macro that does not have to be run manually.

The following is the code I've started working with:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F3:AN3")) Is Nothing Then KeyCellsChanged
End Sub


Private Sub KeyCellsChanged()

   Dim Cell As Object
     For Each Cell In Range("E3")
    Cell.Interior.ColorIndex = 3

   Next Cell

End Sub

However, this macro seems to run regardless of whether the number in the cell is changed, as long as I press enter it highlight E3 as red.

Any help is much appreciated!

Answer 1

Here is my favorite way to detect changes in an Excel VBA app:

1. Create an exact copy of the range you're watching in hidden rows below the range the user sees.
2. Add another section below that (also hidden) with formulas subtracting the user range with the hidden range with an if statement that sets the value to 1 if the difference is anything but 0.
3. Use conditional formatting in the user range that changes the background color of the row if the corresponding change-detection row (or cell) is > 0.

What I like about this approach:

1. If a user makes a change and then reverts back to the original value, the row is "smart enough" to know that nothing has changed.
2. Code that runs any time a user changes something is a pain and can lead to problems. If you set up your change detection the way I'm describing, your code only fires when the sheet is initialized. The worksheet_change event is expensive, and also "may effectively turn off Excel’s Undo feature. Excel’s Undo stack is destroyed whenever an event procedure makes a change to the worksheet." (per John Walkenbach: Excel 2010 Power Programming)
3. You can detect if the user is navigating away from the page and warn them that their changes will be lost.