Highlighting Duplicate Values within N rows

[jonnyg860]

I have a worksheet where employees scan barcodes of ISBN and Price into two columns. Right now I have a simple conditional format to highlight duplicates within each row. Is it possible to highlight duplicate values found within say 10 rows, rather than the entire column. I want it to alert them if they mistakenly scanned the same book twice, but not if it was same title scanned hours earlier. We use office 2010 with windows 7.

Thanks for any help.

 

[CWatts]

You can use this for your conditional formatting starting in the cell on the 10th row and then use the format painter to copy that format down your worksheet.

As the cell moves down, the range will also move down so that the conditional formatting will still work. The test for "" prevents all the blank spaces from being highlighted.

IF(A10="",0,COUNTIF(A1:A10,A10))>1
IF(A11="",0,COUNTIF(A2:A11,A11))>1
IF(A12="",0,COUNTIF(A3:A12,A12))>1

The only drawback is that you can't use this in Rows 1-9 because if you move backwards, the cell tries to reference A0 and then A-1, which are not valid cell names and you'll get a #REF error, but this will work for all but the first 9 rows.

 

[jonnyg860]

Thanks, I'll try it when I get to work in the morning, I appreciate it.