Highlight cells with dependent cells?
Highlight cells with dependent cells?
(OP)
Without using VBA (I am not familiar with VBA), is there an easy way to highlight or mark all the cells in a worksheet that have dependant cell(s)?
I am trying to debug a very complicated worksheet, and that would help a lot.
I am trying to debug a very complicated worksheet, and that would help a lot.





RE: Highlight cells with dependent cells?
It would actually be more useful to have a way to highlight or mark all cells that do not have dependant cell(s).
RE: Highlight cells with dependent cells?
Under the Tools menu select Forumula Auditing. This will allow you to trace dependents, precedents, and errors. Activating the Formula Auditing toolbar makes the feature convenient to use for large debugging tasks.
RE: Highlight cells with dependent cells?
It's built-in!
Menu - Tools|Auditing|Trace Dependents
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk
RE: Highlight cells with dependent cells?
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk
RE: Highlight cells with dependent cells?
I was actually looking for a way to mark every cell simultaneously, without showing the dependant arrow(s). otherwise, I would have to select every cell on the worksheet to show dependents individually, and there are hundreds of cells (too much work). I do not need to see specifically what cells were precedent. In other words, if every cell had depedent tracer arrows visible, it would be hard to spot which cells do not have dependents.
RE: Highlight cells with dependent cells?
RE: Highlight cells with dependent cells?
Thanks but that won't work either. Most cells have formulas - I just need to see the "broken" cells that don't have dependent cells, due to errors incurred while adding-on/patching this worksheet over a long time.
RE: Highlight cells with dependent cells?
RE: Highlight cells with dependent cells?
I am sure learning Visual Basic can be done. However, this worksheet had grown more complicated over 8 years of improvements, and I am afraid it would take a long time to convert it to VBA. (See my previous thread "How do I reduce File Size?").
For future Excel projects, I would be less leery of using VBA>
RE: Highlight cells with dependent cells?
unfortunately, there is no technique to accomplish the goal you've stated without writing VBA.
while i do not remember the website, there is a fellow whom wrote vba to conduct trace precedents or trace dependents of worksheets. i believe the results merely indicate how many dependents/precedents exist for a particular cell - formula, and not where they are located. from memory, the fella's name is Aaron Blood. just did a web search and olah, my memory is correct!
try: http://www.xl-logic.com/menu.html
download the explode.zip file.
sometimes it is best to simply start another workbook to reduce file size. i often acomplished this for "old" or extensively modified workbooks and the results are worth the exercise.
lastly, having nothing to gain or loose, i would be willing, able, and would volunteer to simplify the workbook for you upon request.
good luck!
-pmover
RE: Highlight cells with dependent cells?
This brings up a window that will automatically select all cells that have, for example, constants, or formulas, or text.
It's a really useful tool, not very talked about.
This may help you...
tg
RE: Highlight cells with dependent cells?
pmover- thnks for the offer, but you wouldn't want to tangle with this. I am essentially re-doing it from scratch onto a new worksheet; I wanted to fully verify the old version because the precedents/dependents are so "convoluted". I am making slow progress, but the new version looks much more efficient, because I am a lot better at formula and logic-statement writing now than I was 8 years ago when this thing started.
RE: Highlight cells with dependent cells?
If you select a single cell, then edit/go to/dependent cells (direct or all levels), Excel will select all these, and you can for example change their colour, and start debugging.
tg
RE: Highlight cells with dependent cells?
OK I see that. I also found that pressing the Auditing Toolbar "dependant cells" button repeatedly does the same thing, but with tracer arrows.
RE: Highlight cells with dependent cells?
good that you are persistent in resolving the matter. fyi, i am not shy nor bashful in tangling with a "convoluted" workbook. afterall, the application does what it is instructed or programmed to do.
i came across this website and thought you would be interested in what the author has to say about reducing large excel files.
http://www.ozgrid.com/Excel/ExcelProblems.htm
fyi, this website, along with others, has significant and useful information for one to practice.
good luck and do not hesitate to ask!
-pmover
RE: Highlight cells with dependent cells?
www.techstreet.com/