Highlighting cell with data source using 2 sheets (warehouse bin location)

[Dhanks13]

Hi guys,

Need your help! what will be the formula on this scenario? I have 2 sheets (1 for BIN LOCATION and the other one was DATA SOURCE. From data source the cell of BIN LOCATION sheet will be filled with the color of the said LEGEND below. For exp. BIN LOCATION "

ATH-A63-01​

with the remarks of GOODSTOCKS the cell of sheet 1 for Location ATH-A63-01 will be filled with the color of ​

GREEN.

​

See attached file for your reference.

Thanks a lot in advance,

LEGEND:
GOODSTOCKS - GREEN
FOR PULL OUT - ORANGE
PULLED OUT - RED


-BIN LOCATION (Sheet 1)
[TABLE="width: 56"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ATH-A63-01[/TD]
[/TR]
[TR]
[TD]ATH-A64-01[/TD]
[/TR]
[TR]
[TD]ATH-A65-01[/TD]
[/TR]
</tbody>[/TABLE]


-DATA SOURCE (Sheet 2)
[TABLE="width: 360"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]17-May-18[/TD]
[TD]ATH-A63-01[/TD]
[TD]GOODSTOCKS[/TD]
[/TR]
[TR]
[TD]17-May-18[/TD]
[TD]ATH-A64-01[/TD]
[TD]FOR PULL OUT[/TD]
[/TR]
[TR]
[TD]17-May-18[/TD]
[TD]ATH-A65-01[/TD]
[TD]PULLED OUT[/TD]
[/TR]
</tbody>[/TABLE]