PDA

View Full Version : [SOLVED] Link Excel files with color



hobgoblin
08-17-2005, 07:39 AM
Hi all.

I have 1 excel file with lots of data colored according some criteria. What i want is to create another workbook and link the cell( and its color, not only data) of that workbook to the original file.

Note : Cells do not correspond one file to the other.
EX A1 (in the 1st file) = B4 (in the 2nd)
A2 = C7 , etc. So i can't just copy/paste it.

Also, i cant use conditional fomatting because cells are not colored according to their value.

Any ideas ? Help is greatly appreciated. http://www.mrexcel.com/board2/images/smiles/icon_eek.gif


P.S All data is entered and formatted manually.

hobgoblin
08-17-2005, 07:41 AM
Also, copy/ paste special will not work because the positions of the cells are not the same:

Ex : 1st file:
A1 B1 C1
A2 B2 C2

Correspond :
F6 G8 F2
A1 A2 A7

shades
08-17-2005, 09:13 AM
Howdy. How are the cells colored now? What is the criteria?

If you have that criteria, it is relatively easy to incorporate that into VBA.

hobgoblin
08-17-2005, 10:18 AM
No there is no criteria. Not on the value of the cell anyway. What i have is a list of products. And lets say those that are sold are 1 color, that are preordered another, etc. there is no way to know it. It is all done manually. What i want to avoid is to have to redo it in another excel file just because the disposition of the cells is not the same. Also, my boss does not want a c&p method. Dont ask me why, it just is. What he asked is a linked file, in which data AND COLOUR is updated automatically according to teh source file ( the same color). So that's about it. if anyone knows how to do it, please feel free to help. :whistle:

N.B. VB solution would be fine also.

shades
08-17-2005, 02:46 PM
Okay. I was confused (not hard to accomplish!), since in the OP you had written:

I have 1 excel file with lots of data colored according some criteria.

How much are you talking about (how many cells)? Is the data all contiguous (no empty columns, rows)? VBA would require an array, checking both content and ColorIndex, and then recreating based on that combination.

I'm sure there are some who can do this. My time doesn't permit that right now.

hobgoblin
08-18-2005, 10:13 AM
Shades thanks for your replies.

Ok the answers to your questions are:
1. Many Many Many cells. (about 1000 rows by 20 columns)
2.Yes the data that i need is contiguous.

Hope someone knows how to do it. All i need is an example. Let's say a code to link 2-3 cells. I'll probably be able to do the rest.

Edit : The location of the source file and the cell number can be taken from the formula of the destination cell. Also, it is important that the source file does not have to be open at the same time.

hobgoblin
08-22-2005, 08:28 AM
Guys i still need help on this. :help I just dont know where to start. If someone can just show me an example it would be very appreciated. : pray2: Thanks.

geekgirlau
08-23-2005, 03:43 PM
Is this what you're after?



Sub GetColour()
' Purpose: Capture the interior colour of the linked cell
Dim c As Range ' all cells
Dim strMaster As String ' name of master file
Dim strPath As String ' path of master file
Dim strCurrent As String ' name of current file
Dim strSheet As String ' linked sheet
Dim strCell As String ' linked cell
strPath = "H:\my documents"
strMaster = "Test1.xls"
strCurrent = ActiveWorkbook.Name
' open master workbook
On Error Resume Next
Debug.Print Windows(strMaster).Visible
If Err.Number <> 0 Then
Workbooks.Open strPath & strMaster, False, True
Windows(strCurrent).Activate
End If
On Error GoTo ErrHandler
For Each c In ActiveSheet.UsedRange
' find cells linked to master
If InStr(1, c.Formula, strMaster) > 0 Then
' find name of linked sheet and cell
strSheet = Mid(c.Formula, InStr(1, c.Formula, "]") + 1)
strCell = Mid(strSheet, InStr(1, strSheet, "!") + 1)
strSheet = Left(strSheet, InStr(1, strSheet, "!") - 1)
With Workbooks(strMaster).Sheets(strSheet)
c.Interior.ColorIndex = .Range(strCell).Interior.ColorIndex
End With
End If
Next c
ExitHere:
Set c = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere
End Sub

hobgoblin
08-24-2005, 08:15 AM
That looks like it. :joy: Thank you very very much.
Will tell for sure after i test it. But it's a great start.
Again :bow::ipray: TY !

hobgoblin
08-24-2005, 12:17 PM
Yup, Thread solved. :D
Again, many, many, many thanks !

geekgirlau
08-24-2005, 03:42 PM
You're welcome!