Linking cells and cell color from another workbook

[oxicottin]

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

 

[Damon Ostrander]

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

 

[oxicottin]

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

 

[Damon Ostrander]

Hi Chad,

To link the cell colors (not a true link--implemented via VBA code upon worksheet calculation) you can use this 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

 

[oxicottin]

Thank you Damon.....

 

[Dana Carey]

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.

 

[clogs]

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

 

[arjun0014]

Thanks dear. I have the same problem in multiple worksheets . Now resolved.!!

 

[Greeshma Ravi]

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

Hi,

As I have inserted here I want to reflect colour from one sheet to the other alone, is it possible to do automatically.

I found a code to do that in one single sheet but I want to link two different sheets, like What ever I colour in April, I want that colour to be automatically reflected to the main sheet (2024 Printable calender). Is it possible?

I will attach the VBA code that I found to reflect colour in the single sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updated By Nirmal
Me.Range("C3").Interior.Color = Me.Range("C5").Interior.Color
Me.Range("D3").Interior.Color = Me.Range("D5").Interior.Color
End Sub

with the above code ot is possible to make automatic colour reflection as shown in the below imag.

Kindly let me know if there is any way to reflect colour automatically

Thank you and Kind regards,
Greeshma Ravi