Color row based on table in other Worksheet

[arelh]

I have 100,000 records in a table (data) in sheet 1 as per below based on "Success" or "Fail"

Table (data)
Event Result
MB65 Success
B65 (AI) Success
MF6.1.1.1.3 Fail
MF6.1.1.1.3.1 Fail
MF6.1.1.1 Fail

I need to colour a row based on another table (_MF6) which has 22 possible events - in this case only row containing "MF6.1.1.1.3.1" would be coloured as it is a "Fail"

Table _MF6
MF6.1.1.1.3.1
MF6.1.1.1.3.2
MF6.1.1.1.3.3
MF6.1.1.1.4.1
MF6.1.1.1.4.2
MF6.1.1.1.4.3
etc

 

[Aryatect]

Hi, I think you might be looking for something like this:

Book1
	A	B	C	D	E
1	Table (data)				Table _MF6
2	Event	Result			MF6.1.1.1.3.1
3	MB65	Success			MF6.1.1.1.3.2
4	B65 (AI)	Success			MF6.1.1.1.3.3
5	MF6.1.1.1.3	Fail			MF6.1.1.1.4.1
6	MF6.1.1.1.3.1	Fail			MF6.1.1.1.4.2
7	MF6.1.1.1.3.3	Success			MF6.1.1.1.4.3
Sheet1

https://drive.google.com/open?id=1zzvcZzD6ls7fZxlhKwZchStaRTdVeq_J

https://drive.google.com/open?id=1a933L7J-N8bhcBHixX-7MOYLQv-QDNo_

https://drive.google.com/open?id=1Q1x3hhurBVjBqqBestvDezouc5UelyTY

https://drive.google.com/open?id=1tD5f3U90Ukz7FOyBTqlZe30Rfuay8fEu

 

[arelh]

Thanks Aryatect - I have used conditional formatting, but I need the table (data) to be colour coded not (_MF6)

Additionally there are 4 other tables (_MF7, _MF9, _MF10, _MB10) which have a total of 100 permutations, therefore I was looking to code in VBA if possible

 

[Aryatect]

ohkk, I am not that good with VBA but someone here sure will be able to help you. Just to suggest here if reverse is required below can be used:

Book1
	A	B	C	D	E	F	G	H
1	Table (data)			Table _MF6		Table _MF7		Table _MF8
2	Event			MF6.1.1.1.3.1		MF6.1.1.1.2		MF6.1.1.1.7
3	MB65			MF6.1.1.1.3.2		MF6.1.1.1.4		MF6.1.1.1.8
4	MF6.1.1.1.4.2			MF6.1.1.1.3.5				MF6.1.1.1.3.3
5	MF6.1.1.1.3			MF6.1.1.1.4.1
6	MF6.1.1.1.3.1			MF6.1.1.1.4.2
7	MF6.1.1.1.3.3			MF6.1.1.1.4.3
8
Sheet1

https://drive.google.com/open?id=1v_cb0JCixVTKWdA8pL8FtSOFSRDQaHK2

Conditional Formatting for Success: =IFERROR(MATCH(A3,$D$2:$D$7,0),0)+IFERROR(MATCH(A3,$F$2:$F$7,0),0)+IFERROR(MATCH(A3,$H$2:$H$7,0),0)
For Fail: =(IFERROR(MATCH(A3,$D$2:$D$7,0),0)+IFERROR(MATCH(A3,$F$2:$F$7,0),0)+IFERROR(MATCH(A3,$H$2:$H$7,0),0))=0