Conditional Format or VBA code - would need help with both. I want to change the fill colour of a cell based on another cell's content (text)

[netrixuser]

I just can't get my head around how to approach this.
I have inherited a spreadsheet containing a floorplan that is using Vlookups to pull the name of the person sitting at the desk from an array containing the desks and usernames. I would like to change the fill colour of the cell that represents the desk based on some text contained in another column in the lookup array. The text could be either T4 or T4M and I would like the fill colour to be different for each one.

Below is an example of the Desk layout and the Array below that. [The Vlookup references an cell containing both first and second names]
Based on the text in column I in the array, I would like the cells in the plan (where the names are presented) to change colour accordingly.

I can use Conditional Formatting in each cell but cannot see how to copy it to every other cell so that the referenced cell changes each time [ie to look at L1-2D01D for the cell containing Micky and L1-2D02D for Donald] . The Desk Plan, as you can imagine, is fairly large, but is not contiguous rows or columns of desks - but rather blocks of desks.

I'm happy to run a Macro as and when changes are made if that is an easier approach.

Any pointers/help gratefully received !

Regards, NetrixUser

Realised I missed the headings off - "Micky Mouse" is J58, "Donald Duck" is K58

 

[Snakehips]

@netrixuser

Maybe along these lines....

MRXLMAY21.xlsm
	A	B	C	D
1
2	L1-2B01D
3	L1-2B02D	Donald	Trump	T4
4	L1-2B03D
5	L1-2B04D
6	L1-2D01D	Micky	Mouse	T4
7	L1-2D02D	Donald	Duck	T4M
8	L1-2D03D
9	L1-2D04D
Sheet7 (2)

MRXLMAY21.xlsm
	J	K	L	M	N	O	P	Q
57	L1-2D01D	L1-2D02D	L1-2D03D	L1-2D04D
58	Micky Mouse	Donald Duck
59			L1-2B02D
60			Donald Trump
61
62
Sheet7 (2)

Cell Formulas
Range	Formula
J60:L60,J58:M58	J58	=IFERROR(VLOOKUP(J57,$A$2:$D$9,2) & CHAR(10) & VLOOKUP(J57,$A$2:$D$9,3),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J57:Q62	Expression	=VLOOKUP(J57,$A$2:$D$9,4)="T4M"	text	NO
J57:Q62	Expression	=VLOOKUP(J57,$A$2:$D$9,4)="T4"	text	NO

Hope that helps.

 

[netrixuser]

Hi Snakehips, thank you for your time and your reply.

It did and you are a star !!!

Thanks again