pulling color from cell of workbook to another linked workbook
 

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!

pulling color from cell of workbook to another linked workbook
 

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!

pulling color from cell of workbook to another linked workbook
 

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!

pulling color from cell of workbook to another linked workbook
 

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!

pulling color from cell of workbook to another linked workbook
 

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!

pulling color from cell of workbook to another linked workbook
 

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!