Use VBA to highlight cells with a dependency in another sheet

[Bryce]

Hi,


I need to write a macro to highlight all cells within a selected range that have a dependency in either the same worksheet or elsewhere in the workbook on another worksheet.


I believe this can be achieved by using the navigate arrows (.navigatearrow) however I’m not sure how to put this in to action.


Bryce

 

[Hui]

Bryce


Firstly, Welcome to the Chandoo.org Forums


Have you seen/used the Excel Audit Tools?

It is available at the Formulas, Trace Dependents tab

 

[NARAYANK991]

Hi ,


Just BTW , you may want to look at this :


http://www.jkp-ads.com/RefTreeAnalyser.asp


Narayan

 

[Bryce]

Thanks Hui and Narayank991. Nice to be on board. The only catch with the Trace Dependents button in the Formula Auditing tab (Excel 2010) is that it can only be used on the active cell one at a time and that you end up with arrows all over the place and I have thousands of cells to audit. All I need to do is highlight any cell that has another cell referring to it from the same or another sheet. I believe this can be acheieved with the .navigatearrow method however not sure where to start. The RefTreeAnalyser looks useful too, once I've highlighted all the cells with dependencies. APpreciate the link.

 

[Bryce]

Hi Narayan, I just noticed that the RefTreeAnalyser does not work with 64Bit Excel 2010. Have you seen a similar product that works for the 64Bit version?