highlights cells that are not a dependents
 

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

highlights cells that are not a dependents
 

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



"rajesh" wrote:

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

highlights cells that are not a dependents
 

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

"rajesh" wrote in message
...
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

highlights cells that are not a dependents
 

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.



"rajesh" wrote:

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

highlights cells that are not a dependents
 

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

Tom

"Rick Rothstein (MVP - VB)" wrote:

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

"rajesh" wrote in message
...
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

highlights cells that are not a dependents
 

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" wrote in message
...
Just noticed your post. Will this pick up dependencies on other
worksheets?

Tom

"Rick Rothstein (MVP - VB)" wrote:

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

"rajesh" wrote in message
...
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

highlights cells that are not a dependents
 

You are making me dizzy!

Tom

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
Just noticed your post. Will this pick up dependencies on other
worksheets?

Tom

"Rick Rothstein (MVP - VB)" wrote:

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

"rajesh" wrote in message
...
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

highlights cells that are not a dependents
 

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


"TomPl" wrote in message
...
You are making me dizzy!

Tom

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
Just noticed your post. Will this pick up dependencies on other
worksheets?

Tom

"Rick Rothstein (MVP - VB)" wrote:

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

"rajesh" wrote in message
...
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

highlights cells that are not a dependents
 

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.

"TomPl" wrote:

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.



"rajesh" wrote:

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

highlights cells that are not a dependents
 

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


"rajesh" wrote in message
...
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.

"TomPl" wrote:

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.



"rajesh" wrote:

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

highlights cells that are not a dependents
 

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

highlights cells that are not a dependents
 

Hi Rick

Thats really amazing. It works perfectly.

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

Regards
Rajesh

"Rick Rothstein (MVP - VB)" wrote:

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

highlights cells that are not a dependents
 

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


"rajesh" wrote in message
...
Hi Rick

Thats really amazing. It works perfectly.

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

Regards
Rajesh

"Rick Rothstein (MVP - VB)" wrote:

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

highlights cells that are not a dependents
 

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/archi...-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

On Mon, 18 Aug 2008 01:48:50 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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


"rajesh" wrote in message
...
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.

"TomPl" wrote:

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.



"rajesh" wrote:

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

highlights cells that are not a dependents
 

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


"Lars-Åke Aspelin" wrote in message
...

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/archi...-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

On Mon, 18 Aug 2008 01:48:50 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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


"rajesh" wrote in message
...
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.

"TomPl" wrote:

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.



"rajesh" wrote:

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