Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    Question Unanswered: Conditional Formatting

    Hi,

    I wonder can someone help me, I want to apply conditional formatting to highlight rows in a range of data based on 2 conditions.

    1st Condition - if Load Number occurs more than once
    AND
    2nd Condition - does each occurrence of the Load Number have a different Supplier

    If the answer to both conditions is Yes, then highlight row.

    See example:

    Column A
    A1: Load Number
    A2: EL1
    A3: EL1
    A4: EL2
    A5: EL3
    A6: EL4
    A7: EL4

    Column B
    B1: Supplier
    B2: A
    B3: A
    B4: B
    B5: C
    B6: D
    B7: A

    Thanks in advance,
    Morfoman

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    Which version of Excel are you using?

  3. #3
    Join Date
    Mar 2010
    Posts
    7
    I am using Excel 2007 but I also have 2003 if needs be.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    1st Condition - if Load Number occurs more than once
    AND
    2nd Condition - does each occurrence of the Load Number have a different Supplier

    If the answer to both conditions is Yes, then highlight row.
    The second condition together with the text data types makes this a little tricky.

    Select the range A2:B7 per your example > Home tab on the ribbon > conditional formatting > new rule > use a formula >
    Code:
    =AND(COUNTIF($A$2:$A$7,$A2)>1,
        COUNTIF($A$2:$A$7,$A2)=(SUM(IF(FREQUENCY(IF($A$2:$A$7=$A2,
        MATCH("~"&$B$2:$B$7,$B$2:$B$7&"",0)),ROW($B$2:$B$7)-ROW($B2)+1),1))))
    Add whatever formatting you need. As it stands, this does not allow for empty cells in the range.
    Last edited by Colin Legg; 07-22-10 at 08:29.

  5. #5
    Join Date
    Mar 2010
    Posts
    7
    Hi,

    Thanks for this code, I would have been a long time putting this together. With a few tweeks I got it working.

    Thanks

Posting Permissions

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