How to find and highlight duplicate rows based on 3 columns

0
votes

does anyone have VBA code to highlight duplicate rows based on three columns?

I'm using a sheet called "Billing Voucher (Part 1)" I would like to create a button that highlights duplicate rows in blue (only based on three columns) when pressed.

For example, 

   | D   E   I
---+-------------
 1 | 1   1   1
 2 | 1   2   7
 3 | 7   9   1
 4 | 1   1   1

Based on the above, the program should highlight row 1 and 4.

I'm not sure why is it so difficult to find the right code to highlight duplicates.

I cant use conditional formatting because it slows my computer tremendously, and a lot of the reusable code seems to either be overly complex, or does something else.

I've seen so many instances of code, isn't there a simple way to create the program without being overly complex

1
excelvba
Why vba, Conditional Formatting will do it. – Scott Craner
I've tried using conditional formatting, and it absolutely works. The problem i'm having is that it makes my worksheet extremely slow. – Mike
I'm not sure VBA being faster than conditional formatting. – Dominique
What was the formula you used for conditional formatting? – Scott Craner

1 Answers

0
votes

No strict need for VBA ...

  1. create one more column as the concatenation of all key fields
  2. sort the concatenation column
  3. create a dup-check formula in yet another column (looking for equality in the row above) - like so:

enter image description here

all rows with result > 1 are the nth repetitions of the key.

After elimination of duplicates (or whatever processing) you can restore the original sort order by sorting on SortKey