Highlighted Cell on duplicate by multiple cln [Solved/Closed]

Report
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
-
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
-
Hello,





Kindly help me i stuck in a problem, I have an Excel Sheet which contains Sales Figure an insight of the excel sheet is as:

Cust. id Bill Date Itempurchased
00001 1-05-2012 Shoes
00010 1-05-2012 Shirt
00011 2-05-2012 Shoes
00010 2-05-2012 Shirt
00001 2-05-2012 Shoes
00010 3-05-2012 Pant
00001 4-05-2012 Shoes
00011 4-05-2012 Pant

I want to colr cell if Cust. id and Itempurchased are same. e.g row2 & row4 and Row1, row5 & row7. Please Suggest me if it possible by Conditional formatting or Macro. any help is highly appreciated.

Regards,
Rahul

3 replies

Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
Hi Raul,

The tricky part in your request in my opinion is the choice of colors.
I started out with orange, then realizing that the color needs to change or else your whole table might turn orange. So then I added a counter to change the ColorIndex number.

So you might want to full around with the ColorIndex a bit. If you need further assistance then let me know which colors you are looking for.

Here is the code:
Sub ColorDuplicate()
Dim lRow, x, y, z As Integer
Dim vCheck, wCheck As String

lRow = Range("A" & Rows.Count).End(xlUp).Row
x = 1
z = 0

    Do
    x = x + 1
    y = 1
    z = z + 1
    vCheck = Range("A" & x).Value & Range("C" & x).Value
    
        Do
        y = y + 1
        wCheck = Range("A" & y).Value & Range("C" & y).Value
        If vCheck = wCheck And x <> y Then
        Range(Cells(x, 1), Cells(x, 3)).Interior.ColorIndex = 45 + z
        Range(Cells(y, 1), Cells(y, 3)).Interior.ColorIndex = 45 + z
        End If
        Loop Until y = lRow
          
    Loop Until x = lRow

End Sub

Best regards,
Trowa
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
118
Thanks you very much Trowa,

by your help i have discover a formula if we put that in conditional formatting and use it in conditional formatting if formula is true:

=COUNTIFS($A$2:$A$999,A2,$C$2:$C$999,C2)

Then set the required format.

This will help our readers who are not comfortable with Macros. The drawback of this method is that we only use single color format.

Thanks again Trowa
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
Glad to be helpfull Rahul.

See you around,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!