highlights cells that are not a dependents

[rajesh]

Hi

I would like to highlight cells that are not having any dependents (i.e.,
any other cell in the same worksheet or another worksheet does not depend on
the cell to be validated).

any possible solution through vba / macro is of great help.

thanks in advance

 

[Barb Reinhardt]

I'd think you could do a search for = and the cells that aren't selected,
don't have dependents.

 

[Rick Rothstein \(MVP - VB\)]

What "non-dependent" cells do you want to highlight... to the ends of the
worksheet? I doubt that; so, for the code below, I am going to assume you
have selected the range of cells that you are interested in determining
whether there are dependents or not. Another question... in what way did you
want to "highlight" the cells not having dependents... color the interior,
select them, something else? For the code below, I'll assume you want to
select them. That means to use the code below, you will need to select the
cells you are interested in, run the macro (press Alt+F8, select the macro,
run it) and you will get a new selection consisting of only those cell with
no dependents.

Sub SelectNonDependentCells()
Dim C As Range
Dim R As Range
Dim NonDependents As Range
On Error Resume Next
For Each C In Selection
Set R = C.Dependents
If Err.Number > 0 Then
If NonDependents Is Nothing Then
Set NonDependents = C
Else
Set NonDependents = Union(C, NonDependents)
End If
End If
Err.Clear
Next
NonDependents.Select
End Sub

Rick

 

[TomPl]

VBA cannot recognize remote dependencies (i.e. it only recognizes
dependencies on the active sheet). The best solution I can think of is to
show dependencies for all cells on the worksheet because that will show
remote dependencies as well. Manually this would be a tediuos task but this
code will do it for you.

Sub CellsDeps()

Dim rng As Range

For Each rng In ThisWorkbook.ActiveSheet.UsedRange
rng.ShowDependents
Next rng

End Sub

Of course you can "Unshow" all the dependencies with Tools - Formula
Auditing - Remove All Arrows.

Hope this helps.

 

[TomPl]

Just noticed your post. Will this pick up dependencies on other worksheets?

Tom

 

[Rick Rothstein \(MVP - VB\)]

Hmm, no it apparently won't. I thought I might try and code a general
solution by iterating the worksheets, then iterating the cells in

SpecialCells(xlCellTypeFormulas)

on each sheet, using InStr to check each formula for the address of the cell
in the selection. But then I realized I actually had to do 4 checks (one for
each absolute/relative setting for the row and column) and these along with
the selected cell's worksheet name concatenated in front with a "!"
concatenated between them; however, I also realized I had to account for
those cases where the worksheet name needed to be surrounded by apostrophes.
I figured this would be a lot of work, but doable... and I almost started to
code this all up when I realized I would also have to account for named
constants for the cells and/or worksheets. It was at this point I decide it
would be far too much work to do it this way.

Rick

 

[TomPl]

You are making me dizzy!

Tom

Hmm, no it apparently won't. I thought I might try and code a general
solution by iterating the worksheets, then iterating the cells in

SpecialCells(xlCellTypeFormulas)

on each sheet, using InStr to check each formula for the address of the cell
in the selection. But then I realized I actually had to do 4 checks (one for
each absolute/relative setting for the row and column) and these along with
the selected cell's worksheet name concatenated in front with a "!"
concatenated between them; however, I also realized I had to account for
those cases where the worksheet name needed to be surrounded by apostrophes.
I figured this would be a lot of work, but doable... and I almost started to
code this all up when I realized I would also have to account for named
constants for the cells and/or worksheets. It was at this point I decide it
would be far too much work to do it this way.

Rick

 

[Rick Rothstein \(MVP - VB\)]

How do you think I felt!<g> I really thought I had a "simple" way to
approach the problem and then all the "what ifs" started to come to mind.

Rick

 

[rajesh]

Hi Tom

You are correct what i wanted was tracing dependents on other sheets (not
only active sheet.

Your code is working perfectly. but works for entire sheet, i need only
selected range to be traced.

Thanks for your help.

 

[Rick Rothstein \(MVP - VB\)]

It took a little studying to figure out what was going on (Tom's post about
removing the arrows put me on what I think was the right track). Give this
macro a try; I think it will do what you want...

Sub SelectNonDependentCellsInSelection()
Dim ShapeCount As Long
Dim R As Range
Dim NonDependents As Range
ActiveSheet.ClearArrows
ShapeCount = ActiveSheet.Shapes.Count
For Each R In Selection
R.ShowDependents
If ActiveSheet.Shapes.Count = ShapeCount Then
If NonDependents Is Nothing Then
Set NonDependents = R
Else
Set NonDependents = Union(R, NonDependents)
End If
End If
ActiveSheet.ClearArrows
Next
NonDependents.Select
End Sub

Rick

 

[Rick Rothstein \(MVP - VB\)]

Give this macro a try; I think it will do what you want...

Let me be clear about the above statement... the code I posted appears to
account for dependents from other sheets as well as the active sheet... that
is, the code should do what you originally asked for.

Rick

 

[rajesh]

Hi Rick

That’s really amazing. It works perfectly.

Thanks a lot !!!!!!!!!

Regards
Rajesh

 

[Rick Rothstein \(MVP - VB\)]

You are quite welcome... I only wish you didn't sound so surprised at my
having been able to answer the question you asked. LOL... only kidding.<g>

Actually, I want to thank you for asking the question in the first place.
This gave me an opportunity to learn an aspect of Excel that I hadn't had to
deal with before and, in the process, develop a new technique for examining
the worksheet which may come in handy in other questions down the line. It
is questions that like this, the ones that force me to "stretch" my
knowledge of Excel, that I look forward to... it is what makes volunteering
to answer question on these newsgroups fun for me.

Rick

 

[Lars-Åke Aspelin]

I like this macro very much, but the naming makes me confused.

The macro selects, as requested by the OP, all the cells that have no
cells that are dependent of them, yes, but that is exactly the
complement of what
"Go to Special -> Precedents" described on this page
http://blogs.msdn.com/donovans/arch...g-precedent-and-dependent-cells-method-2.aspx
does, so to me it would be more natural if the macro would be named
SelectNonPrecedentCellsInSelection rather than ...NonDependent...
Alternatively SelectCellsInSelectionThatHaveNoDependents

Having Dependents is the same thing as being a Precedent (not a
Dependent) and not having any Dependent is the same thing as being a
NonPrecedent (not a NonDependent), right?

Having Precedents is the same thing as being a Dependent and
not having any Precedents is the same thing as being a NonDependent,
right?

Sorry if this is just a language understanding problem on my side?

Lars-Åke

 

[Rick Rothstein \(MVP - VB\)]

I like this macro very much...

Thank you. I kind of like it too.<g> Once it dawned on me what the
ShowDependents method was actually doing with the line work on the
worksheet, the code sort of wrote itself after that.

...but the naming makes me confused.

The 'NonDependentCells' part of the name was meant to mean "cells with no
dependents", but you are probably right that there is probably a better name
to be had. However, being a macro (unlike a function), the code is
independent of the macro's name... the user is free to rename the macro to
anything they want and the underlying code will not be affected (my use of
SelectNonDependentCellsInSelection was simply a descriptive-type name for
the macro's functionality which I used solely for example purposes).

Rick