Create a Macro to look at value entered in cell, against table for cell coloured in green

[surkdidat]

Hi

I have a table from A2:F50.

In cell H2, i want the use to input a value, and in Cell H3, I want it to look at the table and bring up the value.

However, I only want it to look at cells coloured in Green ( 146 / 208 / 80 #92D050 )

Then in the results cell I need it to provide the column header found in row 2 of the table (The cell will be green in the below example not the font colour)

So, I enter "DOG" in H2, I want "ANIMAL2" to populate in H3.

Or, I enter "HORSE" in H2, and brings up "Animal 3"

There maybe occasions, when its in 2 places so I would like H3 to return something like (for pig) = "Animal 2 ; Animal 3" or "Animal 2 or Animal 3"

Thanks in advance

	Column A	Column B	Column C	Column D
Row 1
Row 2	Animal	Animal 2	Animal 3	Animal 4
Row 3	CAT	CAT	CAT	CAT
Row 4	DOG	DOG	DOG	DOG
Row 5	HORSE	HORSE	HORSE	HORSE
Row 6	PIG	PIG	PIG	PIG

 

[kvsrinivasamurthy]

Without see ing the file it is not possible to get solution. With the font color index in the file only we can write the code.

 

[surkdidat]

How can i attach a sample file please?

 

[surkdidat]

Green Cell Lookup.xlsx

Shared with Dropbox

www.dropbox.com

 

[kvsrinivasamurthy]

UDF (User DefinedFunction) is better than Macro. So i have given Code for UDF.
Code:

Function GetColorHeader(IpRng As Range, Cri As String) As String
Dim Rng As Range, Cel As Range, K%
For Each Cel In IpRng
If Cel.Interior.ColorIndex <> -4142 Then
K = Range("B4").Interior.ColorIndex
Exit For
End If
Next Cel

For Each Cel In IpRng
If Cel.Interior.ColorIndex = K And UCase(Cel) = UCase(Cri) Then
GetColorHeader = IpRng.Cells(1, Cel.Column - IpRng.Column + 1)
Exit For
End If
Next Cel
End Function

In I2 enter UDF
=GetColorHeader($A$2:$D$6,H2)

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List
Save file as .xlsm