Highlight Rows of Duplicate results column with unique coloring

Here's a way to do what I think you're asking

Please Login or Register  to view this content.

Note: people on here will likely give you much better solutions that avoid using Selection, this is just how I know how to do it. Also, some of the "Duplicate groups" will have a color index assigned to their background, but it will be white/light grey or some other hard to see color

Thank you, Daniel

Sorry for any confusion in the initial details, and you are correct. It is the second option you clarified, that in column F there are values duplicate to others - creating "groups".
And yes, "Row 1" (I'm working on little sleep at the moment).

I will try your script shortly and let you know.
Thanks again,
Glen

Originally Posted by danielexcelvba

Here's a way to do what I think you're asking

Please Login or Register  to view this content.

Note: people on here will likely give you much better solutions that avoid using Selection, this is just how I know how to do it. Also, some of the "Duplicate groups" will have a color index assigned to their background, but it will be white/light grey or some other hard to see color

Hello again, Daniel,
I ran the script and it returned Run-time error '13': Type mismatch.
(it also dropped into cell J2 a value of "1653666" ?)
The Debug highlighted the issue here: If Cells(j, 6).Value <> Cells(i, 6).Value Then

Glen

Originally Posted by scottiex

How would this be useful?

regardless try this.

...
End Sub[/CODE]

Scottiex, thanks for the attempt but it resulted in the last record in a group being highlighted, and after about 20 lines every line was highlighted irrespective of duplicate values.
To answer your question, this is useful as we are merging two data sets from different MCOs which share services and part numbers. We need to easily identify where a service and part number repeated for audit purposes.

This morning I found the following script which is very close to what I want but it is looking duplicates in column A not F. I'm not sure how to alter it in this regard.

Sub HighlightUniqueDuplicatesGC()
Dim sh As Worksheet, c As Range, clr As Variant, lr As Long
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
clr = Array(3, 4, 6, 7, 8, 10, 12, 13, 14, 15, 16, 17, 18, 19, 20, 22, 23, 24, 26, 27, 28, 31, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 50, 53, 54)
With sh
.Range("A1:A" & lr).AdvancedFilter xlFilterCopy, , .Range("B" & lr + 2), True
x = LBound(clr)
For Each c In .Range("B" & lr + 4).CurrentRegion
.UsedRange.AutoFilter 1, c.Value
If Application.CountIf(.Range("A:A"), c.Value) > 1 Then
.Range("A2", .Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Interior.ColorIndex = clr(x)
End If
.AutoFilterMode = False
x = x + 1
If x > UBound(clr) Then x = LBound(clr)
Next
.Range("B" & lr + 2).CurrentRegion.Delete
End With
End Sub

Originally Posted by scottiex

OK seems I misunderstood your requirement.

messy coding but here is a quick fix that seems to work... I see they limited the colours presumably to retain contrast.

Please Login or Register  to view this content.

"To answer your question, this is useful as we are merging two data sets from different MCOs which share services and part numbers. We need to easily identify where a service and part number repeated for audit purposes."

I see why they would ask for that, I sometimes get asked for that sort of thing. But there should be a better way. I presume once they find a duplicate they take some action and I imagine having a helper column with some indicator of what to do would be more useful.

Thanks for the followup Scottiex.
I ran the new code but it yielded a runtime error. I tried pre-selecting (highlighting) the range before running the code as well, to see if that would limit but same error.
I am attaching an excerpt of sample data which I have manually highlighted lines to what I am hoping the end result would be (again, based on column F being duplicate. more obvious in the last records).
Thanks again.

Originally Posted by scottiex

yes you have errors in your data.
#N/A

here is a lazy workaround but the obvious solution is to not have errors.
also adjusted how the filter works to work with your data

Please Login or Register  to view this content.

This has totally solved it! Thank you Scottiex
Yes, the errors are a result of a vlookup without data present. This will be an ever-present circumstance as the Item will either be present in one or both MCO's which are merged into this one sheet.
Thanks for everyone's input and Scottiex ftw!

Glen