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

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
480
Office Version
  1. 2016
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 AColumn BColumn CColumn D
Row 1
Row 2AnimalAnimal 2Animal 3Animal 4
Row 3CATCATCATCAT
Row 4DOGDOGDOGDOG
Row 5HORSEHORSEHORSEHORSE
Row 6PIGPIGPIGPIG
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
909
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.
 

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
909
UDF (User DefinedFunction) is better than Macro. So i have given Code for UDF.
Code:
VBA 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
 

Forum statistics

Threads
1,176,500
Messages
5,903,415
Members
435,028
Latest member
excelvbanoob420

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top