Copy the format of a cell reference
 

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

Copy the format of a cell reference
 

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
--
- K Dales


"Jim" wrote:

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