Detecting changed cells on calculation

[jackerman09]

My Goal: The accountants that I work with like to mark cells of interest by changing the background color to yellow. They asked that I set up a macro that, if the value in a yellow cell changes, it then becomes red (If a value in a non-yellow cell changes it doesn't matter). I tried using this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Interior.ColorIndex <> xlNone Then
Target.Interior.ColorIndex = 3
End If
End Sub

However, this is only checking the cell that was manually changed b/c this is the only target cell. Is there a way for it to check any cell that changed, even if it changed b/c it contained a formula that was dependent on another cell (i.e. it contained a sum function), determine if it is yellow, and if so, make it red. I want it to ignore any cell that is not highlighted yellow whether it changes or not.

Any thoughts?

Thanks in advance!

[RonMcK]

How do cells become Yellow? Only by the accountants manually changing? Or can conditional formatting produce a yellow cell?

This may help those who will help you. I'm interested in what solutions they offer you.

Cheers,

[jackerman09]

They will only become yellow if they manually make them yellow.

Generally, it would be a sum function on a summary tab that needs to be made red if it changes, however, the precedents for the sum will most likely be on another sheet.

[PhilC]

I would suggest the creation of either a macro button, or (this will slow down data entry) add a selection change event macro that stores all of the yellow highlighted values. When the sheet that has those yellow highlights is next accessed the worksheet_activation event triggers a macro to compair the previously stored values to the current values and rehighlight the changed cells in Red.

I'd suggest the button as you can let the end users know when they select all of the "Watch" cells they click the macro and Excel will then "Watch" those values. Click it again to turn it off like a toggle switch, that way they can make changes as needed and then re-highlight and turn the macro back on.

Just my 2 cents.

Phil

[jackerman09]

Thanks everyone for the input, here's what I ended up doing:

Here is the code I used. It was designed so that the accountants using it will be able to highlight cells (generally in yellow, but any color besides no fill has the same affect) and the macro will determine if the cell was changed, and then it will play through, one by one, each cell dependent on the changed cell, and if it was changed, highlight it in red. For each of these cells, it will determine if it was changed (and if it was, and it was yellow), and then it will determine if this cell has any dependents, and do the same, and so on and so on.



That's basically it aside from a few other random requests (how to handle blank cells, groups of cells, unhighlighted cells), but it got complicated when trying to follow dependents from one sheet to another (ended up using .navigatearrow, which worked well) and when dependents had dependents (used an array which compiled all of the dependents, their address, their original value, and a boolean that tells whether or not they have been assessed, then went back through the array and assessed each one based on their new values).
Feel free to ask any questions b/c I know how horribly I just explained that. I also don't think I'm about to format the below code correctly, so if anyone has any suggestions let me know. I've been learning VBA for almost a year now, so if anyone has any questions/suggestions let me know I'm always happy to talk about it.


Oh also this needs to be put into each worksheet module for which you want the cells changes to be reflected (does not need to be in the worksheet module for a summary page that only has cells dependent on other cells).




And so... the code...


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dep As Variant, Deps As Variant, OldValue As Variant, DepSheet As String, DepAdd As String, DepOldValue As Double
Dim NewValue As Variant

Application.ScreenUpdating = False

Deps = Array(0, 0)
ReDim Deps(0 To 3, 0 To 0)

NewValue = Target.Formula

With Application
.EnableEvents = False
.Undo
End With
OldValue = Target.Value

If Target.Cells.Count > 1 Then
Application.Undo
GoTo 8
End If

Target.ShowDependents
For iLinkNum = 1 To 1000
For iArrowNum = 1 To 1000
On Error Resume Next
Err.Number = 0
Target.NavigateArrow TowardPrecedent:=False, ArrowNumber:=iArrowNum, LinkNumber:=iLinkNum
If Err.Number <> 0 Then GoTo 7
If Target.Address = ActiveCell.Address Then Exit For
ReDim Preserve Deps(3, UBound(Deps, 2) + 1)
Deps(0, UBound(Deps, 3)) = ActiveCell.Worksheet.Name
Deps(1, UBound(Deps, 3)) = ActiveCell.Address
Deps(2, UBound(Deps, 3)) = ActiveCell.Value
Deps(3, UBound(Deps, 3)) = False
Next iArrowNum
Next iLinkNum

Target.Value = NewValue
With Application
.Calculate
.EnableEvents = True
End With

If Target.Interior.ColorIndex <> xlNone Then
If Target.Value <> OldValue And OldValue <> "" Then
Target.Interior.ColorIndex = 3
End If
End If

For x = 1 To UBound(Deps, 2)
DepSheet = Deps(0, x)
DepAdd = Deps(1, x)
DepOldValue = Deps(2, x)
If ThisWorkbook.Worksheets(DepSheet).Range(DepAdd).Value <> DepOldValue Then
If ThisWorkbook.Worksheets(DepSheet).Range(DepAdd).Interior.ColorIndex <> xlNone Then
ThisWorkbook.Worksheets(DepSheet).Range(DepAdd).Interior.ColorIndex = 3
End If
End If
Next x

Target.ShowDependents (True)
Target.Offset(1, 0).Select

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub