Highlighting consecutive duplicates

[aimezeyp]

I have a sheet of about 30000 lines, I am trying to find a way to highlight when I have the same entry in both column C and D eg.

23297 23297 H0572 C0154
23296 23296 H0572 G0083
21850 21850 H0681 S0729
21851 21851 H0681 S0730
23288 23288 H0571 S0705
23289 23289 H0571 S0706
21886 21886 H0681 S0694
21842 21842 H0681 S0728
23198 23198 H0703 S0312
23202 23202 H0704 G0037
23203 23203 H0704 G0088
23201 23201 H0704 S0312
23616 23616 H0705 C0161
34118 34118 H0705 C0161
23284 23284 H0570 G0083

So the red I would need highlighting, but not the blue as the H code is not the same

I hope that makes sense!

[xlnitwit]

Hi,

You can do that with conditional formatting using a formula. Assuming your data is sorted on columns C and D and starts in row 2 with headers in row 1, you would use a formula like
=OR(AND($C1=$C2,$D1=$D2),AND($C3=$C2,$D3=$D2))
applied to the range from A2 to the last cell in column D.

If the data is not sorted, and can't be, the formula would need to change to something like
=COUNTIFS($C:$C,$C2,$D:$D,$D2)>1

[benishiryo]

welcome to the forum. i was headed towards xlnitwit's first solution too because of your subject header, but it seems like you don't want the blue highlighted because column C is not the same? and not because they are not consecutive? if so, select the range you want to apply to (say from A2:D16)
go to Home tab -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=COUNTIFS($C:$C,$C2,$D:$D,$D2)>1
format

[aimezeyp]

thank you both, benishiryo that work's perfectly. You're right I was getting confused with the consecutive as I was thinking of sorting it in such a way that they would be consecutive. Your way works no matter what!