Color row based on table in other Worksheet

arelh

New Member
Joined
Dec 14, 2004
Messages
17
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi, I think you might be looking for something like this:


Book1
ABCDE
1Table (data)Table _MF6
2EventResultMF6.1.1.1.3.1
3MB65SuccessMF6.1.1.1.3.2
4B65 (AI)SuccessMF6.1.1.1.3.3
5MF6.1.1.1.3FailMF6.1.1.1.4.1
6MF6.1.1.1.3.1FailMF6.1.1.1.4.2
7MF6.1.1.1.3.3SuccessMF6.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
 
Upvote 0
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
 
Upvote 0
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
ABCDEFGH
1Table (data)Table _MF6Table _MF7Table _MF8
2EventMF6.1.1.1.3.1MF6.1.1.1.2MF6.1.1.1.7
3MB65MF6.1.1.1.3.2MF6.1.1.1.4MF6.1.1.1.8
4MF6.1.1.1.4.2MF6.1.1.1.3.5MF6.1.1.1.3.3
5MF6.1.1.1.3MF6.1.1.1.4.1
6MF6.1.1.1.3.1MF6.1.1.1.4.2
7MF6.1.1.1.3.3MF6.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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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
Back
Top