1. Registered User
Join Date
Mar 2010
Posts
7

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

Morfoman

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

Which version of Excel are you using?

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

4. Registered User
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 07:29.

5. Registered User
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
•