how do I create a link for both content and format of the cell?

[Becky]

When I insert a link to a cell in Excell, the destination cell shows data
content but not the format of the source cell. How do I create a link so that
the destination cell shows both data content and format of the source cell?

Thank you for your help.

 

[Gary''s Student]

Linking only picks up the value of the source cell. It is possible, using
VBA to determine if a cell is a link, reach back to the source, copy the
format and paste it back into the destination cell:


Sub format_painter()
Dim r, r2 As Range
Dim s As String
Dim b As Boolean
For Each r In Selection
If r.HasFormula Then
s = r.Formula
s = Right(s, Len(s) - 1)
On Error Resume Next
Set r2 = Range(s)
On Error GoTo 0
b = Not r2 Is Nothing
If b Then
r2.Copy
r.PasteSpecial Paste:=xlPasteFormats
End If
End If
Next
End Sub