Linking cells and cell color from another workbook

oxicottin

Board Regular
Joined
Feb 21, 2007
Messages
126
Hello, I have a question and wanted to know if it was possibe and if so how. I wanted to link cells from one workbook to another (Which I know how to do) the problem im having is the linking works but why doesnt the cell color chang like it is in the workbook im linking from? Example: If A1 has text (Me) with the cell being green then the linked workbook would only have the text (Me) and not the green cell. How would I do this?

Thanks,
Chad
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi Chad,

How to do this depends on the source of the cell color in the linked workbook. If it is from conditional formatting, then the conditional formatting must be "pseudo-" linked. Otherwise, only the cell color needs to be transferred. Either way a few lines of VBA code are required. Let me know the source of the cell color and I'll provide the code.

If the source of the color can be either cell formatting or conditional formatting, it is still possible but then requires extra code.

Damon
 
Upvote 0
Damon, thanks for the reply! Ok, the Workbook sheet that data is entered into is actually a calendar sheet and on days like vacations, personal days and several other special occasions we color the square with that special occasion. There is no code or formatting for the coloring of that cell or any cell on the sheet I just have a chart and you copy and paste from that chart to the cell you want to color. The way I linked the the two was I highlighted some cells and went to my "Linked" sheet in a seperate workbook and then right clicked Paste Special/Paste Link.

I have another question, I moved my linked workbook and it lost its link is there someway to notify me that my linked workbook is no longer linked because there is a slight chance someone might move thier attendance workbook on the server.

Thanks,
Chad
 
Upvote 0
Hi Chad,

To link the cell colors (not a true link--implemented via VBA code upon worksheet calculation) you can use this code:

Code:
Private Sub Worksheet_Calculate()
   'apply cells colors from single-cell formula dependencies/links
   Dim Cel     As Range
   Dim RefCel  As Range
   On Error Resume Next
   For Each Cel In ActiveSheet.UsedRange
      If Cel.HasFormula Then
         Set RefCel = Evaluate(Mid(Cel.Formula, 2))
         Cel.Interior.Color = RefCel.Interior.Color
      End If
   Next Cel
End Sub

Place this code in the destination worksheet's event code module. To do this, right-click on the worksheet's tab, select View Code, and paste this code into the Code pane. The colors will then update to the source cells' colors when the worksheet links recalculate. You can force this by re-entering any one of the linked cell formulas.

Regarding your linked workbook question, I will ponder this a bit and get back to you.

Damon
 
Upvote 0
I was wondering if you could post the solution for carrying the conditional formatting (in my case...cell fill color) across multiple worksheets. I've got one Worksheet where I have four conditional formatting rules displaying my cell colors based on the data in another column (and those are working beautifully)....I just can seem to figure out the code to carry those colors onto another Worksheet that is using cell validation to pull it's values from the first sheet. The dropdown displays all of the info I want, just not the original color.
 
Upvote 0
Hi Damon,
If I wanted to maintain the font color instead of the cell color , what modification would be required to the code in the sub ?

regards,
Martin
 
Upvote 0

Forum statistics

Threads
1,212,148
Messages
6,106,241
Members
448,009
Latest member
Brgrant820

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top