jmd7
asked on
VBA/Excel 97 Cell Dependents/Precedents
Hi, I'm working on a macro that would graphically mark the cells within a given sheet that have either at least one precedent or at least one dependent cell.
I used the Cells.DirectDependents/Dir
for the active sheet only. Apparently, the Precedents/Dependents located on different sheets than the active one are not included within the Count (though I can invoke them using the Dependents/Precedents
menu bar button).
I replaced the Precedents.Count test with the Cells.HasFormula test, which works fine, but I can't find
any workaround for the Dependents case.
Any ideas would be appreciated.
I used the Cells.DirectDependents/Dir
for the active sheet only. Apparently, the Precedents/Dependents located on different sheets than the active one are not included within the Count (though I can invoke them using the Dependents/Precedents
menu bar button).
I replaced the Precedents.Count test with the Cells.HasFormula test, which works fine, but I can't find
any workaround for the Dependents case.
Any ideas would be appreciated.
ASKER
Thanks tesserhex,
though it seems I did not put my point clear enough, sorry.
I think your code tests if each of the cells has a precedent (the HasFormula line) and if so, sets the cell's background to red and displays the dependent/precedent arrows.
What I need to achieve, though, is to set the color of the cell to red also in case the cell has no precedent, but it has at least one dependent - i.e. at least one other cell referres to the tested cell (I don't need to display the arrows at all). I'm afraid your code does not cover this case.
For me it worked with Dependents.Count, but for dependent cells within the SAME SHEET only.
Thanks anyway.
though it seems I did not put my point clear enough, sorry.
I think your code tests if each of the cells has a precedent (the HasFormula line) and if so, sets the cell's background to red and displays the dependent/precedent arrows.
What I need to achieve, though, is to set the color of the cell to red also in case the cell has no precedent, but it has at least one dependent - i.e. at least one other cell referres to the tested cell (I don't need to display the arrows at all). I'm afraid your code does not cover this case.
For me it worked with Dependents.Count, but for dependent cells within the SAME SHEET only.
Thanks anyway.
ASKER
Thanks tesserhex,
though it seems I did not put my point clear enough, sorry.
I think your code tests if each of the cells has a precedent (the HasFormula line) and if so, sets the cell's background to red and displays the dependent/precedent arrows.
What I need to achieve, though, is to set the color of the cell to red also in case the cell has no precedent, but it has at least one dependent - i.e. at least one other cell referres to the tested cell (I don't need to display the arrows at all). I'm afraid your code does not cover this case.
For me it worked with Dependents.Count, but for dependent cells within the SAME SHEET only.
Thanks anyway.
though it seems I did not put my point clear enough, sorry.
I think your code tests if each of the cells has a precedent (the HasFormula line) and if so, sets the cell's background to red and displays the dependent/precedent arrows.
What I need to achieve, though, is to set the color of the cell to red also in case the cell has no precedent, but it has at least one dependent - i.e. at least one other cell referres to the tested cell (I don't need to display the arrows at all). I'm afraid your code does not cover this case.
For me it worked with Dependents.Count, but for dependent cells within the SAME SHEET only.
Thanks anyway.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ouch, by design, understood ;-)
The code appears a 'bit' cumbersome, but it DOES work. Thanks for help.
The code appears a 'bit' cumbersome, but it DOES work. Thanks for help.
Ouch, cumbersome, understood >8o)
Of course, you can remove the bells and whistles like:
- Switch user selection / used range
- Unprotecting
- Excel 97 bug fix
But you still need a two step approach.
Of course, you can remove the bells and whistles like:
- Switch user selection / used range
- Unprotecting
- Excel 97 bug fix
But you still need a two step approach.
ASKER
Yeah, in fact I didn't really mean to comment on the quality of the code as such at all. I just hoped there might be a simple property I missed, instead of having to jump forth and back (which is no way your fault, of course :-))
Thanks a lot again.
Thanks a lot again.
I did not take offence. See my profile. And additionally, I am stuck with Excel 97 at work, perhaps the newer versions have improved in this area.
And, I still can not say I use it, seems to be down for the moment, but could be useful for you: www.mgmoreira.com
And, I still can not say I use it, seems to be down for the moment, but could be useful for you: www.mgmoreira.com
Sub prec_dep()
On Error Resume Next
For r = 1 To 100
For c = 1 To 10
If Cells(r, c).HasFormula = True Then
Cells(r, c).Select
Selection.ShowPrecedents
Selection.ShowDependents
Cells(r, c).Interior.ColorIndex = 3
End If
Next c
Next r
End Sub
Will loop through cells on the active worksheet in the range r = 1 to 100 and c = 1 to 10 and change the background color of the cells where formulae exist to red as well as including the precedent / dependent arrows.
It could be made as advanced as you like.
Hope this helps get you on the way
J.