Color row based on table in other Worksheet
Results 1 to 4 of 4

Thread: Color row based on table in other Worksheet

  1. #1
    New Member
    Join Date
    Dec 2004
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Color row based on table in other Worksheet

    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

  2. #2
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Color row based on table in other Worksheet

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

    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=1zz...ZchStaRTdVeq_J

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

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

    https://drive.google.com/open?id=1tD...lZe30Rfuay8fEu
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    New Member
    Join Date
    Dec 2004
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Color row based on table in other Worksheet

    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

  4. #4
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Color row based on table in other Worksheet

    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:

    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_...8FtSOFSRDQaHK2

    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 by Aryatect; Aug 8th, 2019 at 10:10 PM.
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •