-
Dependents from multiple sheets?
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?
[VBA]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[/VBA]
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.
-
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.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules