Search code examples
excelvbaduplicateshighlightworksheet-function

Highlight Duplicate Column ONLY When Specific Text is Present

I only want to highlight duplicate rows ONLY if the cell in a column contains a specific value.

For example, in Column 3 if the cell contains "Apple" and Columns 1 and Columns 2 match, highlight the rows.

enter image description here

The formula for conditional formatting I'm using looks like 

=COUNTIFS($A$1:$A$1000,$A1,$B$1:$B$1000,$B1,$C$1:$C$1000,"Apple")>1

This formula highlights the whole row if ANY of the criteria is met, so it'll look like, notice Column1 on the last row:

enter image description here

I've googled so many times, but I can't seem to find an answer.

Solution

  • use this formula, you need to include the value in column c also:

    =COUNTIFS($A:$A,$A1,$B:$B,$B1,$C:$C,"Apple",$C:$C,$C1)>1

    enter image description here