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.
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.