Index match with conditional formating

[SAXON10]

Hi,

Good evening Excel Legends,

I am trying to apply conditional formatting to a column in Excel using the "Use a formula to determine which cells to format" feature.

In DATA sheet have a list of names in column A with corresponding reference numbers in column B and type in column C. In DATA sheet I have a longer list of reference numbers in row G4. I want the conditional formatting rule to look up the reference numbers in DATA Sheet against the row G4 and highlight the cell conditional formatting

File attached for your reference. Please assist me.

https://www.dropbox.com/s/78v00v0lbpcgpxk/ME.xlsx?dl=0

DATA;

SUBJECT	ID NO	TYPE	COLOR CODE
AA1	3320	MATHS		HIS
AA2	3321	MATHS		MATHS
AA3	7320	MATHS		SCI
AA4	7520	MATHS		ENG
AA5	7521	MATHS
AA6	WAIT
AA7	9900	MATHS
AA8	6500	MATHS
AA9	6586	ENG
AA10	6530	HIS
AA11	8600	MATHS
AA12	5321	MATHS
AA13	WAIT
AA14	2255	SCI
AA15	8581	SCI
AA16	8580	MATHS
AA17	5320	MATHS
AA18	5325	SCI
AA19	1100	SCI
AA20	2233	MATHS
AA21	2230	HIS
AA22	9500	MATHS
AA23	9531	MATHS
AA24	9680	MATHS
AA25	9681	SCI
AA26	3500	ENG
AA27	3501	ENG
AA28	4500	MATHS
AA29	4501	SCI
AA30	4502	ENG
AA31	4503	ENG

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

DESIRED RESULT

SUBJECT	AA1	AA2	AA3	AA4	AA5	AA6	AA7	AA8	AA9	AA10	AA11	AA12	AA13	AA14	AA15	AA16	AA17	AA18	AA19	AA20	AA21	AA22	AA23	AA24	AA25	AA26	AA27	AA28	AA29	AA30	AA31
ID NO	3320	3321	7320	7520	7521	WAIT	9900	6500	6586	6530	8600	5321	WAIT	2255	8581	8580	5320	5325	1100	2233	2230	9500	9531	9680	9681	3500	3501	4500	4501	4502	4503

<colgroup><col><col span="5"><col><col span="3"><col span="3"><col><col span="18"></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

I think you can
Select B2:B32
Ctrl+C
Select H4
Paste Special; All, and check Transpose

M.

 

[SAXON10]

thank for your reply. There is no another way. I have a more data. Its more hard to apply transpose every time. Please help me.

 

[Marcelo Branco]

I think you should
Turn-on the macro recorder
Do the copy + paste/transpose manually
Turn-off the macro recorder
So you have a code to automate the process

M.

 

[SAXON10]

I think you should
Turn-on the macro recorder
Do the copy + paste/transpose manually
Turn-off the macro recorder
So you have a code to automate the process

M.

thanks for your response and reply. I followup your suggestion.