pulling color from cell of workbook to another linked workbook

[Guest]

I am having difficulty pulling background and font colors from one workbook
to a linked workbook... I have individual "weekly schedule" sheets for each
employee, and link them to a master timesheet which pulls only certain
information over to be used in scheduling meetings.... Everything is working
great except they employees place a background color in the cell to refer to
a project that is ontime, overbudget, misc... there are 7 colors they use as
codes....
I would greatly appreciate any help I can get, I have tried so many things
and cannot seem to pull colors over!

 

[JE McGimpsey]

You'd need to use a macro, as functions can only return values, not
formatting.

How best to do it probably depends on how you're "pulling" the
information from the one workbook, e.g.: is it in a continuous block, or
scattered throughout your summary?

 

[Guest]

scattered....I believe, each cell is linked to another cell in other
workbooks....

I DO appoligize for the multiple post, I had errored out and thought it had
not been accepted... sorry for the confusion...

 

[JE McGimpsey]

One (brute force) way:

Put this in your summary worksheet code module:

Private Sub Worksheet_Activate()
Dim vSheet1Refs As Variant
Dim i As Long
Dim nSource As Long
Dim nDest As Long

'First array is source sheet cells, second is
'The corresponding summary sheet cells
vSheet1Refs = Array(Array("A1", "B2", "C3"), _
Array("J1", "J2", "J3"))
nSource = LBound(vSheet1Refs, 1)
nDest = nSource + 1
With Sheets("Sheet1")
For i = LBound(vSheet1Refs(nSource)) To _
UBound(vSheet1Refs(nSource))
.Range(vSheet1Refs(nSource)(i)).Copy
Range(vSheet1Refs(nDest)(i)).PasteSpecial _
Paste:=xlFormats
Next i
End With
End Sub

 

[Guest]

I have pasted this and it still doesnt work, do I have to change sheet
numbers and array letter/numbers as well? I am not so sure about excel!
thanks for your help

 

[JE McGimpsey]

Yes - change them to your sheet name and cell numbers. You will likely
have to add cells to the inner arrays.