PDA

View Full Version : Dependents from multiple sheets?



jackerman09
10-06-2010, 07:09 AM
I am trying to use the below code to create an array which compiles the values of each of the target cell's dependents. Currently, it is only recognizing dependents on the same sheet as the target cell. How can I have it recognize dependents on other sheets as well?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dep As Variant, Deps As Variant, OldValue As Variant

For Each Dep In Target.Dependents
ReDim Preserve Deps(UBound(Deps) + 1)
Deps(UBound(Deps)) = Dep.Value
Next Dep

x = 0
For Each Dep In Target.Dependents
x = x + 1
If Dep.Value <> Deps(x) Then
If Dep.Interior.ColorIndex <> xlNone Then
Dep.Interior.ColorIndex = 3
End If
End If
Next Dep
End Sub

Also there is some other code between the two for each loops, which is why they are not in the same loop, but it isn't relevant to this question. If anyone's interested though feel free to ask for it.

Jan Karel Pieterse
10-06-2010, 10:49 AM
The only way I know to work your way through all off-sheet dependents is by using the navigateArrow method on all shapes on a worksheet, after issuing the .ShowDependents method.

jackerman09
10-06-2010, 12:46 PM
Worked, thanks!