Back to looping through the cells with formulas.

This will loop through all the formulas on the worksheet and look for precedents
on the same worksheet.

Option Explicit
Sub testme()

Dim myCell As Range
Dim myFormRng As Range
Dim myArea As Range
Dim wks As Worksheet

Set wks = ActiveSheet

'get the cells that have dependent cells
Set myFormRng = Nothing
On Error Resume Next
Set myFormRng = wks.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myFormRng Is Nothing Then
'do nothing
Else
'color the formulas
myFormRng.Interior.Color = vbYellow
For Each myCell In myFormRng.Cells
If myCell.Precedents Is Nothing Then
'skip it
Else
For Each myArea In myCell.Precedents.Areas
If myArea.Parent.Range("a1").Address(external:=True) _
= myCell.Parent.Range("a1").Address(external:=True) Then
myArea.Interior.Color = vbYellow
End If
Next myArea
End If
Next myCell
End If

End Sub

If you're really looking to trace your formulas, you may want to look at Jan
Karel Pieterse's Reference Tree analyzer:
http://www.jkp-ads.com/RefTreeAnalyser.asp

He offers both a demo (free) version and a pay for version. Those are described
on that site.



wrote:

<snipped
If D11 has formula that says "=A1+A3+B4", I want cells D11, A1, A2 &
B4 all to be shaded. maybe I am doing something wrong but all of this
stuff when I run the macros only shades D11 (the one with the actual
formula).

--

Dave Peterson