steve
12-08-2005, 03:44 AM
Hi
:hi:
I have a spreadsheet that has several hundred formulas on one sheet summing certain ranges on another sheet. Is it possible to highlight the cells that its summing using VBA.
Example of a couple of the formulas:
=SUM(Sheet1!CD1633:CD1643)+SUM(Sheet1!CD1786:CD1795)
=SUM(Sheet1!F325:F326,Sheet1!F370,Sheet1!F499:F509,Sheet1!F539,Sheet1!F552: F558,Sheet1!F562:F563,Sheet1!F575:F576,Sheet1!F587,Sheet1!F593:F595,Sheet1! F614:F616,Sheet1!F624:F627,Sheet1!F634:F636,Sheet1!F645:F646,Sheet1!F653:F6 56,Sheet1!F662:F664,Sheet1!F677:F679,Sheet1!F685:F686,Sheet1!F696:F698,Shee t1!F705:F707,Sheet1!F714:F716,Sheet1!F721,Sheet1!F726,Sheet1!F728:F732,Shee t1!F746:F748,Sheet1!F774,Sheet1!F780:F781,Sheet1!F789,Sheet1!F799:F803,Shee t1!F813:F814)+SUM(Sheet1!F820:F821,Sheet1!F837,Sheet1!F841,Sheet1!F852:F853 ,Sheet1!F858:F860,Sheet1!F864,Sheet1!F869:F870,Sheet1!F872:F874,Sheet1!F877 :F879,Sheet1!F895,Sheet1!F900:F905,Sheet1!F913:F914,Sheet1!F919:F920,Sheet1 !F922:F926,Sheet1!F935:F937,Sheet1!F941:F942,Sheet1!F945,Sheet1!F958,Sheet1 !F960:F962,Sheet1!F968:F970,Sheet1!F974,Sheet1!F985)+SUM(Sheet1!F989,Sheet1 !F991,Sheet1!F996:F998,Sheet1!F1003:F1006,Sheet1!F1014,Sheet1!F1021:F1022,S heet1!F1027,Sheet1!F1031,Sheet1!F1054,Sheet1!F1066:F1067,Sheet1!F1072,Sheet 1!F1078)
The spreadsheet was not setup by me and it does not reconcile to Sheet1 so need to find what cells are not included in the formulas.
Thanks for any help :bow:
Steve
:hi:
I have a spreadsheet that has several hundred formulas on one sheet summing certain ranges on another sheet. Is it possible to highlight the cells that its summing using VBA.
Example of a couple of the formulas:
=SUM(Sheet1!CD1633:CD1643)+SUM(Sheet1!CD1786:CD1795)
=SUM(Sheet1!F325:F326,Sheet1!F370,Sheet1!F499:F509,Sheet1!F539,Sheet1!F552: F558,Sheet1!F562:F563,Sheet1!F575:F576,Sheet1!F587,Sheet1!F593:F595,Sheet1! F614:F616,Sheet1!F624:F627,Sheet1!F634:F636,Sheet1!F645:F646,Sheet1!F653:F6 56,Sheet1!F662:F664,Sheet1!F677:F679,Sheet1!F685:F686,Sheet1!F696:F698,Shee t1!F705:F707,Sheet1!F714:F716,Sheet1!F721,Sheet1!F726,Sheet1!F728:F732,Shee t1!F746:F748,Sheet1!F774,Sheet1!F780:F781,Sheet1!F789,Sheet1!F799:F803,Shee t1!F813:F814)+SUM(Sheet1!F820:F821,Sheet1!F837,Sheet1!F841,Sheet1!F852:F853 ,Sheet1!F858:F860,Sheet1!F864,Sheet1!F869:F870,Sheet1!F872:F874,Sheet1!F877 :F879,Sheet1!F895,Sheet1!F900:F905,Sheet1!F913:F914,Sheet1!F919:F920,Sheet1 !F922:F926,Sheet1!F935:F937,Sheet1!F941:F942,Sheet1!F945,Sheet1!F958,Sheet1 !F960:F962,Sheet1!F968:F970,Sheet1!F974,Sheet1!F985)+SUM(Sheet1!F989,Sheet1 !F991,Sheet1!F996:F998,Sheet1!F1003:F1006,Sheet1!F1014,Sheet1!F1021:F1022,S heet1!F1027,Sheet1!F1031,Sheet1!F1054,Sheet1!F1066:F1067,Sheet1!F1072,Sheet 1!F1078)
The spreadsheet was not setup by me and it does not reconcile to Sheet1 so need to find what cells are not included in the formulas.
Thanks for any help :bow:
Steve