Solved: Identify cell which have formula

[Lammutaja]

Hello.
I have next problem:
User enters numerical information into some cell in worksheet.
Some another cells in worksheet have formula, which uses cell which
has been changed.
How to identify all cells which have formula and has been changed?

For example: an user enters "5.4" to cell "A1". Cell "A2" has formula "=A1".
I can identify that cell "A1" has been changed with "Worksheet_Change" event, but how can I fix that cell "A2" has been changed too?
My goal is to receive address of changed cell (which has formula) and change its "interior" property
J?ri.

[Bob Phillips]

Hello.
I have next problem:
User enters numerical information into some cell in worksheet.
Some another cells in worksheet have formula, which uses cell which
has been changed.
How to identify all cells which have formula and has been changed?

For example: an user enters "5.4" to cell "A1". Cell "A2" has formula "=A1".
I can identify that cell "A1" has been changed with "Worksheet_Change" event, but how can I fix that cell "A2" has been changed too?
My goal is to receive address of changed cell (which has formula) and change its "interior" property
J?ri.

Take a look at the Range DirectDependents property, although you may need to walk the tree if you want referred to referred to cells.

[mdmackillop]

For all dependent cells on the same worksheet, try
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Dependents.Interior.ColorIndex = 6
End Sub

[Norie]

You could use the Change event and HasFormula.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.HasFormula Then
Target.Interior.ColorIndex = 3
Else
Target.Interior.ColorIndex = xlNone
End If
End Sub

[mdmackillop]

Just for fun, you could set the colour depending upon the level of dependency! (Use an array for custom colouring)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
i = 6
On Error GoTo Exits
'Clear previous colours
Cells.SpecialCells(xlCellTypeFormulas, 23).Interior.ColorIndex = xlNone
'Reset colours based on heirarchy
Target.DirectDependents.Interior.ColorIndex = i
Do
i = i + 1
Set Target = Target.DirectDependents
Target.DirectDependents.Interior.ColorIndex = i
Loop
Exits:
Set Target = Nothing
End Sub

[Lammutaja]

Thanks to all!
J?ri.