Conditional Formatting - On 2 Cells Values

[nniedzielski]

I am working on a spreadsheet similar to this, and i need the row to light up red if the cells in A and C match another row, but needs to be both values matching another row .

like this: many thanks in advance

 

[Fluff]

How about

=COUNTIFS($A$2:$A$20,A2,$C$2:$C$20,C2)>1

 

[nniedzielski]

How about

=COUNTIFS($A$2:$A$20,A2,$C$2:$C$20,C2)>1

so, that does work, this worksheet is going to keep growing, will likely exceed 100,000 rows, how would i alter the formula so that the worksheet never outgrows the formula?

 

[Fluff]

You could use whole column references, but on that amount of data it may be pretty slow, if not unusable.

 

[nniedzielski]

OK Fluff, that works awesome, now my column D is all True and False like it should be, so now, how do i get the rows to light up red? Google got me nowhere. I followed the video, nothing turned color.

 

[nniedzielski]

HI Fluff, I figured out the formatting part, one issue though, on rows that are blank, it lights up red, how can i avoid those rows?

 

[Fluff]

How about

=AND($A2<>"",COUNTIFS($A$2:$A$20,$A2,$C$2:$C$20,$C2)>1)