Lookup cell colour

[Barhomopolis]

Greetings Everybody

I have a workbook in wich I consolidate 65,535 rows of data from 6 other workbooks using compound lookup. I lookup (using INDEX & MATCH) based on a double criteria. That's not the issue.

The issue is that I need to grab not only the values of the matching cells, but also their colours. I need to it the simplest way possible for the sheet is huge and the lookup function is already too complex.

The lookup formula I'm currently using (nested six times for the six files in a nested IFERROR) looks like this:

=IFERROR(INDEX('Z:\1.xlsx'!Table1;MATCH([@

]&[@Loc];'Z:\1.xlsx'!Table1[Code]&'Z:\1.xlsx'!Table1[Loc];0);4);INDEX('Z:\2.xlsx'!Table2;..........And so on six times for six files.

This copies the value of the cell with certain Code and Location from workbook 1.xlsx
I want something that copies the value AND the colour of the cell that matches the Code and Location.

I appreciate any help.

 

[Trevor G]

Have you looked at conditional formatting if the criteria is set in the other workbooks to set the colour then you should be able to use the same criteria in your look up workbook.

 

[Barhomopolis]

@Trevor G

Thank you for your reply
The colouring of the source cells is not conditional and cannot be turned to conditional because it is based on subjective measures, i.e. the colours of the source cell are independent of their values.

mmm.... But now you gave me an idea... Maybe we can: 1- add a column to the source workbooks with values that change based on the source cells colour; 2- then, lookup the values of that new column in the consolidation workbook; 3- then, conditionally colour the consolidated looked up cells based on the values of the new looked up column...

But then again: how do we do step 1? Is there something to change the value of a cell based on the colour of another cell in the same sheet?
And would that be an easier workaround than solving the original problem?

Thanks again.

 

[Trevor G]

If your going to use a helper column then that is the same as using conditional formatting. What are the variable values of the cell, could you make up a small table to identify the conditions?

 

[Barhomopolis]

There are no conditions... the source cells are coloured manually by the users on a totally subjective basis. I just want the consolidation workbook to see that the user of the source workbook chose to colour a certain cell with a colour, and lookup that cell's value and format the corresponding target cell with the same colour.

 

[Trevor G]

Can you work with this then.

[face=Courier New]Sub cellcol()
Range("E3") = Range("B3").Value
Range("E3").Interior.ColorIndex = Range("B3").Interior.ColorIndex

End Sub
[/face]

 

[Barhomopolis]

Thank you, Trevor G.

Could you please show how would this be used in my particular case:

Lookup value columns: 'Z:\7.xlsx'!Table7

 & 'Z:\7.xlsx'!Table7[Loc]

Lookup columns 'Z:\1.xlsx'!Table1[Code] & 'Z:\1.xlsx'!Table1[Loc]

Source column: 'Z:\1.xlsx'!Table1[CPC]

Destinantion column: 'Z:\7.xlsx'!Table7[CPC]

 

[Barhomopolis]

Guys, I really need to know how to apply this to the multiple files lookup I'm doing here.

Thank you.