VBA to colour cells that link elsewhere

[SquareHouse]

Hello everyone,

I have a sheet with amounts of historical data that then feeds to another sheet where the calculation/modelling then happens. Much of the data in the "data" sheet is never actually pulled into the modelling sheet and I would like to get an idea of which cells in the data sheet are actually used. Given I have many of these excels and many of the data tabs are thousands of lines long its not something I can envisage doing manually..... I wonder if there is a VBA script that I could use to quickly colour any cell in the "Data" tab that is then sourced by any other sheet?

Thanks

 

[Marc L]

Hi,​

​

or faster than coloring cells may be the use of a helper column with a formula returning TRUE or FALSE according if the cells match …​

This formula can be used as well in some conditional formatting in order to change the cell color.​

But the more cells using such formatting, the worksheet becomes heavy and that may slow down usual operations​

so it depends of the rows # within this worksheet …​

 

[SquareHouse]

tricky if the cells have very common data points where the value is the same but isn'
t the actual source). Useful quick and dirty first solution though, thanks