Copy the format of a cell reference

[Guest]

I have cells in my spreadsheet that list other cell addresses (i.e. they
contain text in the format "WorksheetName!CellNumber"... for example
"Worksheet1!C33")

How can I programatically (or with the use of a function) copy the format of
the actual reference cell to the cell containing the text reference.

More specifically, if the remote cell is yellow, I want the local cell to
also become yellow.

I hope this makes sense.

Thanks for your help.

Jim Arnold

 

[Guest]

In the following example the parameter ReferenceCell would be the cell that
contains your reference text:

Sub FCopy(ReferenceCell as Range)
Dim SheetName as String, CellRef as String, EPos as Integer
Dim RemoteCell as Range

With ReferenceCell
EPos = InStr(.Text, "!")
If EPos <> 0 Then
SheetName = Left(.Text, EPos-1)
CellRef = Right(.Text, Len(.Text)-EPos)
Set RemoteCell = WorkSheets(SheetName).Range(CellRef)
RemoteCell.Copy
.PasteSpecial xlPasteFormats
End If
End With

End Sub