Highlighted Cell on duplicate by multiple cln

Solved/Closed
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 - May 24, 2012 at 04:29 AM
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 - May 29, 2012 at 10:20 AM
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

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
May 24, 2012 at 09:51 AM
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
0
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
May 25, 2012 at 01:16 AM
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
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
May 29, 2012 at 10:20 AM
Glad to be helpfull Rahul.

See you around,
Trowa
0