Highlight Formula Reference On Another Sheet

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: Highlight Formula Reference On Another Sheet


    thanks for clariying...I do not doubt that clicking a dashed line is possible I am just saying that it doesn't help me to solve the problem...thanks!!!

    Re: Highlight Formula Reference On Another Sheet


    Not true.......AGAIN.


    Sicarii suggested

    Quote

    Without knowing "why" you want the cells highlighted in the 2nd book, would this be useful to you?


    select your cell containing the formula and goto menu: Tools\Formula Auditing\Trace Precedents, now blue lines will connect to your cells in the active workbook and any references to your other book will have dashed black line with a small sheet icon. If you double click the dashed line it will bring a popup box with all of the links and you can choose one and it will goto the 2nd book and highlight that cell.

    YOU replied

    Quote

    thank you for your reply...i need this formula highlighting to visually "debug" or check the code as I programm the model in excel...unfortunately I already tried the method you proposed and it didnt work...(((

    Re: Highlight Formula Reference On Another Sheet


    Quote

    thanks...but it doesnt work...does it work for you???


    Yes -- it has worked for me in the past on Excel 2002, and does work for me in 2003 and 2007.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

    Re: Highlight Formula Reference On Another Sheet


    cool....I hope it wont sound very outrageous...but do you mind making a screenshot of your workbook when you have the cells highlighted? I need this to make sure we understand each other....thanks!

    Re: Highlight Formula Reference On Another Sheet


    IMHO the best you can do would be the Goto precedents/dependents which Hatman has alluded too (press Ctrl + G whilst on the cell in question and pick your poison).


    What you're asking for is more about the design of Excel itself than how to use it - we can't really fix that here!


    Perhaps some git with 2007 will wade in to tell you how fantastic that is for exactly what you need(?)

    Re: Highlight Formula Reference On Another Sheet


    Quote from shg

    Attached.


    thank you for making this for me...as I see the cells in the second sheet are actually not highlighted,,,,even if the popup window shows their links....looks like there is no hope in this question.....((([hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]

    Quote from Cheeky Charlie

    IMHO the best you can do would be the Goto precedents/dependents which Hatman has alluded too (press Ctrl + G whilst on the cell in question and pick your poison).


    What you're asking for is more about the design of Excel itself than how to use it - we can't really fix that here!


    Perhaps some git with 2007 will wade in to tell you how fantastic that is for exactly what you need(?)



    yep..I was just thinking that this was not such a huge problem and an average addin or a smart method would quickly overcome it....

    Re: Highlight Formula Reference On Another Sheet


    Quote

    ... and an average addin or a smart method would quickly overcome it ...


    Probably so, and I've seen code that Dave (I think) has written that follows off-sheet references. I think the lack of response is that (a) whatever highlighting is done will hose the formatting already applied to the precedents, make it necessary to capture and restore it (including conditional formats), (b) people (including me) don't perceive much value over the existing capability.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

    Re: Highlight Formula Reference On Another Sheet


    Color Cell Formula Precedents


    Here is some VBA code that will color the precedent cells of any single cell formula. Color Cell Formula Precedents

    Re: Highlight Formula Reference On Another Sheet


    thanks for the code Dave but I get these two lines highlighted in red with the syntax error:


    Code
    If .Address = rFormulaCell.Address _
            Then GoTo PrecedentError


    Can you please check if this is correct?
    Also - does this code allow to bring the cell formatting back once the cells have been colored?
    Thanks!!!!!

    Re: Highlight Formula Reference On Another Sheet


    Remove the underscore (_) and put all code on one line -


    Code
    If .Address = rFormulaCell.Address Then GoTo PrecedentError


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: Highlight Formula Reference On Another Sheet


    Quote from Ger Plante

    Remove the underscore (_) and put all code on one line -


    Code
    If .Address = rFormulaCell.Address Then GoTo PrecedentError


    Ger



    thanks!!! it works now!! but, strangely, it uses a1 cell as its starting point instead of the current active cell...do you think you can make it start from the current active cell instead of the a1? also - do you know if it is possible to revert format changes after the macro is run?[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]

    Quote from Cheeky Charlie

    I have to admire your tenacity. You should be in business, not research.



    If you are not referring to Ger - thanks! :) I am actually in business too....

    Re: Highlight Formula Reference On Another Sheet


    Try changing


    Code
    Set rFormulaCell = Sheet1.Range("A1")


    with

    Code
    Set rFormulaCell = activecell


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: Highlight Formula Reference On Another Sheet


    Crtl-Z?


    Honestly mate, I can't help but laugh a bit at this thread. Like we agreed on before, we both use formulas pretty heavily, but maybe you should think about restructuring your excel sheet to use formulas in a different way. If all your formulas that you need to check are located on one worksheet then you could use the key commands to get a visual really quick.


    Long story short, more efficient design will lead to a more efficient debugging.


    Good luck,
    Dave

    Re: Highlight Formula Reference On Another Sheet


    thanks for replying but I would rather hear something more constructive about the problem at hand..I am aware of all layout-complexity interplay that affects the worksheet....CTL+Z DOESNT WORK!!!

    Re: Highlight Formula Reference On Another Sheet


    ok...will do...can you please let me know if it is possible to automatically revert the formatting changes made by this macro?

    Re: Highlight Formula Reference On Another Sheet


    Quote

    ... I've seen code that Dave (I think) has written that follows off- sheet references. I think the lack of response is that (a) whatever highlighting is done will hose the formatting already applied to the precedents, make it necessary to capture and restore it (including conditional formats), (b) people (including me) don't perceive much value over the existing capability.


    Quote

    ok...will do...can you please let me know if it is possible to automatically revert the formatting changes made by this macro?


    The thin line between tenacity and taking it too far...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login