How to highlight cells with dependents in other worksheets?

[ibalis]

Hi,

I am looking for a way to highlight all cells (in all tabs) that are referenced by cells in other tabs, i.e. all cells that have dependents in other tabs (dependents on the same tab are easier to trace).

I am reviewing a lot of models in my job and it would be very helpful if could quickly see which cells are used by other tabs so that I pay more attention to them as they drive other parts of the model. Currently I have to trace dependents cell by cell to check if certain cells are used by other tabs which is very time consuming.

Thanks a lot for your help, I am sure you will be able to help because I am asking the best of the best

PS If you have any recommended sources (books, e-courses, free or paid) for someone to start learning VBA to be used for Excel financial models let me know

[mdmackillop]

Have a look here

[mdmackillop]

By Rick Rothstein

Here is some code I have posted in the past, modified for your condition, which will color the cells red. However, note that these cells are only the ones in the UsedRange for the ActiveSheet. So if the used range was A1:F9 and you had a formula somewhere that referenced A1:A100, only A1:A9 will get colored in. It would take what seems like forever to search every cell on the worksheet looking to see if it is a dependent cell or not.

Sub ColorDependentCellsOnActiveSheetRed()
    Dim ShapeCount As Long, R As Range, DependantCells As Range
    Application.ScreenUpdating = False
    ActiveSheet.ClearArrows
    ShapeCount = ActiveSheet.Shapes.Count
    For Each R In ActiveSheet.UsedRange
        R.ShowDependents
        If ActiveSheet.Shapes.Count > ShapeCount Then
            If DependantCells Is Nothing Then
                Set DependantCells = R
            Else
                Set DependantCells = Union(R, DependantCells)
            End If
        End If
        ActiveSheet.ClearArrows
    Next
    DependantCells.Interior.ColorIndex = 3
    Application.ScreenUpdating = True
End Sub

[ibalis]

That's great!

Is it possible to amend this code to highlight cells with dependents in other sheets only, excluding the active sheet? My intention is to quickly check if there are cells I should keep in mind that are used globally, while reviewing a specific sheet in detail, because otherwise almost all cells will have dependents in the active sheet and all cells will be red.

Thank you so much!

[mdmackillop]

Call this from the first sub

'Based upon
'https://colinlegg.wordpress.com/2014/01/14/vba-determine-all-precedent-cells-a-nice-example-of-recursion/
Sub Omitsamesheet()
 
    Dim rngToCheck As Range
    Dim rngPrecedents As Range
    Dim rngPrecedent As Range
    Dim r As Range
    Dim PrecedentCells As Range
    
    For Each r In ActiveSheet.UsedRange
 
    Set rngToCheck = r
 
    On Error Resume Next
    Set rngPrecedents = rngToCheck.Precedents
    On Error GoTo 0
 
    If rngPrecedents Is Nothing Then
        'Do nothing
    Else
        For Each rngPrecedent In rngPrecedents
            If PrecedentCells Is Nothing Then
                Set PrecedentCells = rngPrecedent
            Else
                Set PrecedentCells = Union(rngPrecedent, PrecedentCells)
            End If
        Next rngPrecedent
    End If
    Next r
    PrecedentCells.Interior.ColorIndex = xlNone
End Sub

[ibalis]

Almost! This excludes all cells with dependents in the same sheet, even those that have dependents in other sheets and need to be highlighted too. Any solution to that?

I appreciate your help!

[mdmackillop]

Can you post a sample workbook?