This blog article is one out of two articles on the same subject.
Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2

Question: How do I only highlight duplicates that occur for the second time or more in a list (Column B) and where an adjacent cell value (Column A) must meet a criteria? See picture below.

Answer: Here is the conditional formatting formula: =IF((COUNT(IF(($B$2:$B2=$B2)*($A$2:$A2=$E$1), 1, ""))>1)*($A2=$E$1), TRUE, FALSE)

How to apply the conditional formatting formula in excel 2007:

1. Select the range (A2:B16)
2. Click "Home" tab on the ribbon
3. Click "Conditional formatting"
4. Click "New rule..."
5. Click "Use a formula to determine which cells to format"
6. Click "Format values where this formual is true" window.
7. Type =IF((COUNT(IF(($B$2:$B2=$B2)*($A$2:$A2=$E$1), 1, ""))>1)*($A2=$E$1), TRUE, FALSE)
8. Click Format button
9. Click "Fill" tab
10. Select a color
11. Click OK!
12. Click OK!

Change the category (E1) and see how different cells being highlighted.

Download excel example file.
highlight-duplicates-where-adjacent-column-meets-criteria-using-conditional-formatting.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

Related posts:

1. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2
2. Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel
3. Highlight duplicates using conditional formatting in excel
4. Highlight the second or more duplicates in two lists using conditional formatting in excel
5. Highlight dates within a date range using conditional formatting
6. Create a list of duplicates where adjacent cell value meets a condition in excel
7. Highlight smallest duplicate value in a column using conditional formatting in excel
8. Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel
9. Highlight duplicate values in a range using conditional formatting in excel
10. Prevent duplicates using dynamic conditional formatting in excel