A
Ali
Dear Rick AND ALL,
The reply you posted at this link
http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us on August 17, 2008 as follows:
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
EXCEL FILE ATTACHED FOR YOUR PROPER UNDERSTANDING AND SOLUTION. ATTACHED
FILE HAVE TWO SHEETS.
Data imported and pasted in the first worksheet named as Data-3 for
accounts. Data in this work linked with the worksheet named as "Audit
Accounts". I want to trace or highlight those cells which have not linked or
have no dependents at Audit accounts worksheet.
I paste this code in the VBA module and run macro but it is not highlighting
the cells which have no dependents to the worksheet no. 2 named as "Audit
Accounts". A message box appears which shows (Object variable or with block
variable not set).
Firstly, I want code work properly then only highlight those cells which
have no dependents to "Audit Account" worksheet because each cell have
multiple dependents at a time.
Request to hear you soon for positive response.
Ali
The reply you posted at this link
http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us on August 17, 2008 as follows:
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
EXCEL FILE ATTACHED FOR YOUR PROPER UNDERSTANDING AND SOLUTION. ATTACHED
FILE HAVE TWO SHEETS.
Data imported and pasted in the first worksheet named as Data-3 for
accounts. Data in this work linked with the worksheet named as "Audit
Accounts". I want to trace or highlight those cells which have not linked or
have no dependents at Audit accounts worksheet.
I paste this code in the VBA module and run macro but it is not highlighting
the cells which have no dependents to the worksheet no. 2 named as "Audit
Accounts". A message box appears which shows (Object variable or with block
variable not set).
Firstly, I want code work properly then only highlight those cells which
have no dependents to "Audit Account" worksheet because each cell have
multiple dependents at a time.
Request to hear you soon for positive response.
Ali