Excel - Macro to highlight Rows with Duplicate values in a cellSolved/Closed

Question

Geetings, 

Kindly help us in creating a macro that highlight rows based on duplicate values in a cell [this cell may contain Alphabets, Numbers or alphanumerics].

For example - 

Column A | Column B
Ajay        |    001
AJ           |    001
VJ           |    002
Vijay        |    002
SJ           |    003
Sanjay     |    003

Note: The rows containing the value [001] on Column B should be highlighed in [Light Turquoise color scheme], and the rows containing value [002] on Column B should be highlighted in [Grey-25% color scheme].
Similarly for the rows containing value as [003] should be highlighed again in [Light Turquoise color scheme]

The patern should be same for remaining duplicate entired in entire sheet i.e. 
[Light Turquoise color scheme]
[Grey-25% color scheme]
...
..
.

The assistance on this request would be highly appreciated.

Thank you and Best Regards - AD


Configuration: Windows XP / Internet Explorer 7.0

TrowaD · Accepted Answer

Hi Stalin,

Take a look at the following sample data (column B):
abc
abc
def
ghi
ghi
ghi
ghi
jkl

If you want the result to be 2 (abc and ghi are counted as duplicates) then the following code will do the trick:
Sub test()
Dim x, cDup, lRow As Integer, skipDup As String
lRow = Range("B1").End(xlDown).Row
x = 1
cDup = 0
Do
    x = x + 1
    If Cells(x, "B").Value = Cells(x - 1, "B").Value And skipDup <> Cells(x, "B").Value Then
        cDup = cDup + 1
        skipDup = Cells(x, "B").Value
    End If
Loop Until x = lRow + 1
MsgBox "Column B contains " & cDup & " Duplicates."
End Sub
If this isn't what you meant, then please provide some sample data of your own along with the desired result.

Best regards,
Trowa

TrowaD · Answer

Hi AD,

So basicly if the number in column B is odd then format 1 and if it's even the format 2.

Use the following formula's in conditional format:
=MOD(B1,2)=1 for odd
=MOD(B1,2)=0 for even

Best regards,
Trowa

ajaydwivediji · Answer

Dear Trowa,

Thank you so much for attending my request and this is amazing no doubt.

But I have a situation over here, [apologies for not including/mentioning this in my earlier illustrative example] that the number that is being used for de-duping is not actualy appearing in an order. 

We have instances where lot of [Odd or Even] numbers are trailing/appearing in a group.

For Example in the following example The name Jyoti/JY is also appearing with an [Odd Id 005] under the [Id 003] on [Column B] -

Column A | Column B 
Ajay | 001 
AJ | 001 
VJ | 002 
Vijay | 002 
SJ | 003 
Sanjay | 003 
JY | 005
Jyoti | 005


In this case we this option may lead us to highlighting two different groups with same color despite to the matter of fact they [their IDs] on [Column B] are different. 

Kindly advise/suggest.

Thank you and Best Regards - AD

ajaydwivediji · Answer

Dear Trowa,

Thank you once again for responding and understanding the condition that we have in our list. We appreciate it.

Your Question/observation #1 - The occurance of the Id is inconsistence i.e. this Id can appear only once, or more than once, but incase of multiple occrance all of them will appear together.  It depends on total number of items grouped into one using this Id.

Your Question/Suggestion # 2 - [just start by coloring Turquoise and when the ID changes use the color Grey and when it changes again turn to Turquoise again etc...] is the best option for such conditions and we want it in this fashion.

Answer to your Question # 3 - We want to color [The Entire Row].

Hope to catch you on Thursday. Please take good care of yourself.

Thank you and Best Regards - Ajay

TrowaD · Answer

Hi Ajay,

That is a nice and clear response. The only thing we haven't discussed is if you have a header or not. Since I didn't see one in your example, I assumed you don't have one. If you do, change the forth line "x=1" into "x=2".

Here is the code:
Sub ColorRows()
Dim lRow, x As Integer

lRow = Range("A" & Rows.Count).End(xlUp).Row
x = 1

Rows(x).Interior.ColorIndex = 34

Loop1:
Do
    x = x + 1
    If Cells(x, "B").Value = Cells(x - 1, "B").Value Then
        Rows(x).Interior.ColorIndex = 34
    Else:
        Rows(x).Interior.ColorIndex = 15
        GoTo Loop2
    End If
Loop Until x = lRow

Loop2:
Do
    x = x + 1
    If Cells(x, "B").Value = Cells(x - 1, "B").Value Then
        Rows(x).Interior.ColorIndex = 15
    Else:
        Rows(x).Interior.ColorIndex = 34
        GoTo Loop1
    End If
Loop Until x = lRow

End Sub
Best regards,
Trowa

ajaydwivediji · Answer

~ Simply Wow !!!~  This is awsome. Every row got highlighed the way these were request and should be.

This some thing never seen any where on web.

Kinldy accept our sincere and humble thanks for such a great help!

Note: There is only one bug that shows a message [I think when the processing is over] but results are perfect though

Following is a message displayed on screen.

Microsoft Visual basic
Run-time error '6':
Overflow
[Continue] [End] [Debut] [Help]

When we press/click on [End], then the sheet is returned back. And results are absolutely fine.

TrowaD · Answer

Hi Paul,

So you want to colour row 2 if B2=C2 and the same for the rows below.

Have you tried Conditional Formatting found under the Start ribbon?

Select row 2, go to conditional formatting, add rule, choose formula and enter: =$B2=$C2, confirm.
Drag row 2 down as far as desired and when the small square appears, choose Apply format only.

Best regards,
Trowa

Excel - Macro to highlight Rows with Duplicate values in a cell

7 responses

Similar discussions

Subscribe To Our Newsletter!