Vlookup + cell color function

[astir]

Hi,

I have now used couple of days trying to find and combine others macros without luck to getting this work so hope somebody could help me with this...

Scenario is that i have in one table a "master" devices having a value and color.
master table looks like:
a12=device_name1, c12=text&color, d12=text&color...etc
a13=device_name2, c13=text&color, d13=text&color...etc

*colors are varying much*

and then I have "child" devices where I would like to get same values and color settings automatically than what "master" device have.
child table looks like:
a146=name_of_child, b146=name_of_master, c146=****cell value and color from master device from same column, triggered from b146****
second row like above

I have found a way to change value but not the color:
**in cell C146**=VLOOKUP($B146;$A$12:$E$38;COLUMN(C12))

maybe best would be a function where I can give those same attributes but it also would change the color.

Thank you really much if you can help!!!
-Astir

 

[astir]

bumps

 

[al_b_cnu]

Hi astir,

Unfortunately a function can only return a value. I had a go yesterday on this, you can determine which cell the function is called from, and also determine the colour of the looked up cell, but sadly cannot return that colour into the calling function's cell.

You could do this with a sheet change event maybe, but would require more details as to the sheet layout, which range of cells would require to be monitored, etc.

 

[astir]

Hi,

here is my master table:

basically weeks are never ending. Row range basically stable but could increase every now and then, same with child elements.

And here is child table where colors should be automatically taken from master table according to name in B column:

thanks for trying to solve this

Br.
-Astir

 

[astir]

anyone know way to do this?

thx
-Astir

 

[astir]

anyone? some suggestions?

 

[astir]

last call for this...

if it helps any, amount of different colors are those 6 colors what can be seen on the top left corner.

And these color could be copied as row, not as a single cell.

please, some experts are needed now...

Thx
Astir

 

[astir]

Re: Vlookup + cell color function !!!new action plan, pls check

Ok, maybe this implementation was impossible.

Here is new one and maybe easier to resolve:

So I decided to use this vlookup for child elements and at the moment I dont get colors for those, only values.

But now, could somebody invent an event which will compere values of master table and child table, and then if value would match then put the same color to child table what it is in the "master table". So vlookup function dont have to be part of event anymore...

but the result would be exatly same what Im trying to find out in the original mail

pls help, this is really blocking issue in here

Thanks and regards
-Astir

 

[al_b_cnu]

Hi Astir,

Try this macro:

Option Explicit
Const msMasterSheetName As String = "Sheet1"
Const msChildSheetName As String = "Sheet1"
Const mlMasterRowStart As Long = 11
Const mlMasterRowEnd As Long = 38
Const mlChildrowStart As Long = 146
Const mlChildRowEnd As Long = 183

Sub CopyColours()
Dim iCol As Integer
Dim lRow As Long
Dim rMaster As Range
Dim rChild As Range
Dim rCur As Range, rFind As Range
Dim wsMaster As Worksheet
Dim wsChild As Worksheet

Set wsMaster = Sheets(msMasterSheetName)
Set wsChild = Sheets(msChildSheetName)

'-- Set Child range --
iCol = wsMaster.UsedRange.Rows.Count
Set rChild = wsChild.Range(Cells(mlChildrowStart, 3).Address & ":" & _
                           Cells(mlChildRowEnd, iCol).Address)

'-- Loop thru child range & copy colour from master if match --
For Each rCur In rChild
    iCol = rCur.Column
    Set rMaster = wsMaster.Range(Cells(mlMasterRowStart, iCol).Address & ":" & _
                                 Cells(mlMasterRowEnd, iCol).Address)
    Set rFind = Nothing
    Set rFind = rMaster.Find(rCur.Value, LookIn:=xlValues)
    If Not rFind Is Nothing Then rCur.Interior.Color = rFind.Interior.Color
Next rCur
End Sub

 

[astir]

Hi,

wow, looks promising but it stuck to this error when I pressed play:

runtime error '9': subscription out of range

and it will highlight this row when I pressed debug:
Set wsMaster = Sheets(msMasterSheetName)


Because im not so familiar with macros here is screenshot of that module. Just make sure that I put it to right place.