PDA

View Full Version : How to highlight cells with dependents in other worksheets?



ibalis
03-31-2017, 08:13 AM
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
03-31-2017, 08:58 AM
Have a look here (http://www.vbaexpress.com/forum/showthread.php?42737-highlighting-precedent-cell)

mdmackillop
03-31-2017, 10:45 AM
By Rick Rothstein (https://www.mrexcel.com/forum/excel-questions/885151-visual-basic-applications-format-cell-if-cell-has-dependents.html#post4278191)


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
04-02-2017, 07:47 AM
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
04-02-2017, 09:50 AM
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
04-02-2017, 02:00 PM
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
04-02-2017, 02:11 PM
Can you post a sample workbook?