ExcelBanter - Using reviewing tools in macros

Aaron,

Highlight your test cells and try this.

Sub SimpleExternalPrecedentTest()
'tests for an exclamation mark implying an external sheet link
'or possibly a link to a cell in a different workbook
'note that this will not work on an area containing named ranges
'that refer to different sheets
'and that it assumes you are not entering text containing "!" in cells
'if you want a more robust routine that will handle named ranges
'check out my xspandxl addin on my web site and look at the
'precedents of selection in other sheets command

Dim strMsg As String
Dim bNoPrecFound As Boolean
Dim rngCell As Range

strMsg = "The following cells have no external precedent" & vbCrLf & vbCrLf
For Each rngCell In Selection
If Not IsEmpty(rngCell.Value) And InStr(rngCell.Formula, "!") = 0 Then
strMsg = strMsg & rngCell.Address & vbCrLf
bNoPrecFound = True
End If
Next rngCell

If bNoPrecFound Then
MsgBox strMsg
Else
MsgBox "All cells have external precedents"
End If

End Sub

HTH,

Robin Hammond
www.enhanceddatasystems.com

"Aaron Cooper" wrote in message
...

I need to be able to tell if any of the used cells in column A do not
have a cell which traces back to them. Basically I have cells in other
sheets that link back to these, it is like a Bibliography or a works
cited, only in Excel. I just need to make sure that everything I have
included in my "Works Cited" has been linked to somewhere else.

Using the record macro I have been able to develop something which
brings up those stupid arrows, but I am looking for something that would
provide me with feedback as to which cells do not comply. Maybe
something that would highlight all of the cells which are used but do
not have something linked to them.

Anybody have any code that would do this? Or at least an idea for where
I should start?

Thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!