Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
Dim c As Range, dp As Range, a As Range, i&, b As Range
Application.ScreenUpdating = False
[B:B].ClearContents 'RAZ
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
If c.HasFormula Then c(1, 2) = "'" & c.FormulaLocal
Next
On Error Resume Next 's'il n'y a pas d'antécédents
Set dp = [A:A].DirectPrecedents 'antécédents
On Error GoTo 0
If dp Is Nothing Then Exit Sub
Set a = dp.EntireRow
For i = a.Rows.Count To 1 Step -1
Set b = Intersect(a.Rows(i), dp)
If Not b Is Nothing Then
For Each b In b
If IsNumeric(b) Then
[B:B].Replace b.Address, b.Text, xlPart
[B:B].Replace b.Address(1, 0), b.Text
[B:B].Replace b.Address(0, 1), b.Text
[B:B].Replace b.Address(0, 0), b.Text
End If
Next
End If
Next
End Sub