Hi Chris,

I ünderstand what your sub would do ... it would help me a great deal
further!
However my VBA(XL97) does not understand "InStr" ? !

Note I think that 'SpecialCells' is only updated after a save. We could then
look up against this list to see if a cell is referenced anywhere ... but
this is starting to get complicated.
I agree. No need for it!

Brgds Sige


Chris Marlow wrote:
Sige,

Yes it is a bit limited. 'Dependents' etc are limited to the active sheet

If you add an extra sheet 'Formula' to the book you can 'catalogue' all the
formulae in it using the following code

Dim xlsWorksheet As Excel.Worksheet
Dim ranCell As Range
Dim lRowPointer As Long
Dim sFormula As String

lRowPointer = 2
ThisWorkbook.Sheets("Formula").Cells.ClearContents

For Each xlsWorksheet In ThisWorkbook.Worksheets

For Each ranCell In xlsWorksheet.Range(xlsWorksheet.Cells(1, 1),
xlsWorksheet.Cells.SpecialCells(xlCellTypeLastCell ))

sFormula = ranCell.Formula

If sFormula < "" Then
If InStr(sFormula, "!") < 0 Then

ThisWorkbook.Sheets("Formula").Cells(lRowPointer, 1) =
Mid(sFormula, 2, InStr(sFormula, "!") - 2)
ThisWorkbook.Sheets("Formula").Cells(lRowPointer, 2) =
Right(ranCell.Formula, Len(sFormula) - InStrRev(sFormula, "!"))
Else
ThisWorkbook.Sheets("Formula").Cells(lRowPointer, 1) =
xlsWorksheet.Name
ThisWorkbook.Sheets("Formula").Cells(lRowPointer, 2) =
Right(sFormula, Len(sFormula) - 1)
End If
lRowPointer = lRowPointer + 1
End If

Next ranCell

Next xlsWorksheet

ThisWorkbook.Sheets("Formula").Cells(1, 1) = "Sheet"
ThisWorkbook.Sheets("Formula").Cells(1, 2) = "Formula"

Note I think that 'SpecialCells' is only updated after a save. We could then
look up against this list to see if a cell is referenced anywhere ... but
this is starting to get complicated.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Sige" wrote:

Hi Chris,

Thanks a lot!
Not that I completely understand it ...
If Err.Number = 1004 Then ???

If the cell is dependent to a cell(s) in another sheet (active wbk);
could it be possible to (in this case: not trigger) trigger these as
well?

I added an IF to exclude empty cells....
Sige

Sub Has_No_Dependents()
Dim ranCell As Range
For Each ranCell In Selection

On Error Resume Next
Debug.Print ranCell.Dependents.Count

If Err.Number = 1004 Then
If Not IsEmpty(ranCell) Then
ranCell.FormatConditions.Add Type:=xlExpression,
Formula1:="=TRUE"
ranCell.FormatConditions(1).Interior.ColorIndex = 3
' ranCell.Interior.Color = vbBlue
End If
End If
Err.Clear
On Error Resume Next
Next ranCell
End Sub