Dynamic Conditional Formatting for Duplicate Values in Column

[muhleebbin]

Hi,

I have the traditional conditional format for finding duplicates using the formula rule in conditional formatting "=COUNTIF($A$4:$A$3000,$A4)>1". Is there a way to make this dynamic where it fits the data that varies month to month. As it currently is, all rows are highlighted below the last row of valid data and it's just bugging me . This month filled to row 259 but last month it filled to row 1267 and will vary each month.

 

[Joe4]

Is it because it is highlighting all of the blank rows as duplicates?
If so, a simple modification to your current formula will handle that, i.e.

=AND(COUNTIF($A$4:$A$3000,$A4)>1,$A4<>"")

 

[muhleebbin]

Thank you very much, that worked like a charm! And yes, it was annoying me cause it was highlighting all the blank rows

 

[Joe4]

You are welcome!
Glad I was able to help.