• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

duplicate Identification and color coding the entire row of duplicated

shantraj.antin@gmail.com

shantraj.antin@gmail.com

Member
I have to work sheets Sheet1 and Sheet2 with set of data. I hva to idnetify duplicate values from sheet2 IDs to sheet 1 Id and color code the same formate like what the sheet 2 deal ID has. I am done till idnetification of duplicates but struck in fromating the color code of sheet 2 line item to sheet 1 line item,


Code is,


Sub findDuplicates()

' code to find duplicates in 2 different worksheets

Dim rng1, rng2, cell1, cell2 As Range

' 4 ranges have been defined

Set rng1 = Worksheets("Sheet1").Range("C:C")

'rng1 defines the existing data in column B and worksheet1

Set rng2 = Worksheets("Sheet2").Range("C:C")

'rng2 defines the imported data in column D and worksheet2

For Each cell1 In rng1

If IsEmpty(cell1.Value) Then Exit For

'check for empty rows. If true then exit the program

For Each cell2 In rng2

If IsEmpty(cell2.Value) Then Exit For

If cell1.Value = cell2.Value Then


cell1.EntireRow.Interior.ColorIndex = cell2.EntireRow.Interior.ColorIndex


End If

'run the looping process

Next cell2

Next cell1

End Sub
 
Hi Shantraj,


Welcome to the Forum.


You need to set color for both cell to apply it. Just a Normal Modification in the Code..

[pre]
Code: 
Sub findDuplicates()
' code to find duplicates in 2 different worksheets
Dim rng1, rng2, cell1, cell2 As Range
' 4 ranges have been defined
Set rng1 = Worksheets("Sheet1").Range("C:C")
'rng1 defines the existing data in column B and worksheet1
Set rng2 = Worksheets("Sheet2").Range("C:C")
'rng2 defines the imported data in column D and worksheet2
For Each cell1 In rng1
If IsEmpty(cell1.Value) Then Exit For
'check for empty rows. If true then exit the program
For Each cell2 In rng2
If IsEmpty(cell2.Value) Then Exit For
If cell1.Value = cell2.Value Then
cell1.EntireRow.Interior.Color = RGB(150, 150, 150)
cell2.EntireRow.Interior.Color = RGB(150, 150, 150)
End If
'run the looping process
Next cell2
Next cell1
End Sub
[/pre]
BTW, there was a small Google Search box in top-right of the page. Just play with it, try to search similar topic. and you may found your answer before posting.


If you want to extend your coding, you may visit this post.

http://chandoo.org/forums/topic/format-cells-with-repeating-values-in-a-similar-manner


Regards,

Deb
 
Hi

I suggest this code (more fast if you have a lot of rows)

[pre]
Code: 
Sub FindDuplicates()
Dim Rng As Range, c As Range, v As Range

Application.ScreenUpdating = False
With Worksheets("Sheet1")
Set Rng = .Range("C2:C" & .Cells(.Rows.Count, 3).End(xlUp).Row)
End With

For Each c In Rng
If c.Value <> "" Then
Set v = Worksheets("Sheet2").Range("C:C").Find(c.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not v Is Nothing Then
c.EntireRow.Interior.ColorIndex = v.Interior.ColorIndex
Set v = Nothing
End If
End If
Next c
Set Rng = Nothing
End Sub
Note that in your code cell2.EntireRow.Interior.ColorIndex without sense if all the row dont have the same color

EDIT

Use the propriety Color in place of ColorIndex for better fidelity in colors.

If you want to color just the usedrange, use this code

' code to find duplicates in 2 different worksheets
Sub FindDuplicates()
Dim Rng As Range, c As Range, v As Range

Application.ScreenUpdating = False
With Worksheets("Sheet1")
Set Rng = .Range("C2:C" & .Cells(.Rows.Count, 3).End(xlUp).Row)

For Each c In Rng
If c.Value <> "" Then
Set v = Worksheets("Sheet2").Range("C:C").Find(c.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not v Is Nothing Then
Intersect(.UsedRange, c.EntireRow).Interior.Color = v.Interior.Color
Set v = Nothing
End If
End If
Next c
End With
Set Rng = Nothing
End Sub
[/pre]
 
Hi Deebraj and mercatog,


Thanks for the coding. but it is resulting in defined color. The requirment is if a row in sheet 2 has red color the duplicate row in sheet 1 should apear in red. Similarly this report has different row had different color in sheet 2. The exact color should reflect in sheet1 for the duplicates. Thank you.
 
Hi Shantraj,


* In this case. One of the sheet must be filled with color. to copy that color in another sheet.

i.e If Sheet2's cell is filled with color then use, which is defined as cell2

cell1.EntireRow.Interior.ColorIndex = cell2.EntireRow.Interior.ColorIndex


* if sheets(2)'s color is applied by any conditional formating then use the same line as

cell1.EntireRow.Interior.ColorIndex = cell2.DisplayFormat.Interior.ColorIndex


* For any other assistance, please upload a sample file, with requirement,

Refer : http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards!

Deb
 
@Shantraj

You wrote "The requirment is if a row in sheet 2 has red color the duplicate row in sheet 1 should apear in red"


Yes, it's what the code does. Did you tried it?


Your mistake was in the line
Code: 
cell1.EntireRow.Interior.ColorIndex = cell2.EntireRow.Interior.ColorIndex


If the entirerow of cell2 doesn't have the same color, it will be a problem

Other point, you loop for all cells, it will take an eternity.

The methode Find, works faster then a loop
 
@ Debraj Roy


Thanks lot


cell1.EntireRow.Interior.ColorIndex = cell2.DisplayFormat.Interior.ColorIndex


is exactly meeting my requirments.


@mercatog


Thaks lot for the responce.
 
Hi, shantraj.antin@gmail.com!


May I suggest you to change your nick name? It's too long and it overlaps the comment area making it unreadable. If you want to keep your email address you can put it in the website field of your profile, then when clicking on you nick on any comment the user will be led to your email.


Regards!
 
Hi Guys,


I have encountered once more challenge, for except red for most of the colors I am not getting the exact shade, for example if my sheet 2 line item has green, the result in sheet 1 line item is differing in shade. How can I get the exact ??


Thanks you
 
Also, try to use the method Find as shown before. The code is faster then whene using Loops. You'll search later to optimize code for maxima whene you've a lot of data and each millisecond will have its importance.


regards.
 
You must log in or register to reply here.
Back
Top