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!

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?


In article ,
Sunshine wrote:

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!

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" wrote:

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?


In article ,
Sunshine wrote:

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!

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



In article ,
Sunshine wrote:

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

"JE McGimpsey" wrote:

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?


In article ,
Sunshine wrote:

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!

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" wrote:

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



In article ,
Sunshine wrote:

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

"JE McGimpsey" wrote:

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?


In article ,
Sunshine wrote:

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!

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

In article ,
Sunshine wrote:

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!