Rob,
Can this script be adjusted to work on the entire A column?
thanks, h
Rob van Gelder wrote:
The following loops through a colour list to highlight cells with duplicates.
08-Dec-09
The following loops through a colour list to highlight cells with duplicates.
Sub test()
HighlightDuplicates Range("A2:A201")
End Sub
Sub HighlightDuplicates(DuplicateRange As Range)
Dim rng As Range, rngF As Range, rngFull As Range, rngLast As Range
Dim lngColorIndex As Long, varColors As Variant
Set rngFull = DuplicateRange
varColors = Array(3, 4, 5, 6, 7, 8, 9)
lngColorIndex = LBound(varColors)
rngFull.Interior.ColorIndex = xlColorIndexNone
Set rngLast = rngFull(rngFull.Rows.Count, rngFull.Columns.Count)
For Each rng In rngFull
If rng.Interior.ColorIndex = xlColorIndexNone Then
If Application.WorksheetFunction.CountIf(rngFull, rng.Value) 1 Then
For Each rngF In rngFull
If rngF.Value = rng.Value Then rngF.Interior.ColorIndex = varColors(lngColorIndex)
Next
lngColorIndex = lngColorIndex + 1
If lngColorIndex UBound(varColors) Then lngColorIndex = LBound(varColors)
End If
End If
Next
End Sub
Cheers,
Rob
irealtymods wrote:
Previous Posts In This Thread:
On Monday, December 07, 2009 7:31 AM
irealtymods wrote:
different color for duplicates!!! how?
i have table full of numbers,
what i need is to find duplicates and mark them with different colors.
so like one numbers with red color, others with green, other yellow and so on.
so each set of duplicates will have own color.
i think it will be easier to see if we have this number so i will then
delete it from all rows.
i just used this formula, but donno how to make scenario above
=COUNTIF(L:L;L27)1
thank you
On Tuesday, December 08, 2009 1:10 AM
Rob van Gelder wrote:
The following loops through a colour list to highlight cells with duplicates.
The following loops through a colour list to highlight cells with duplicates.
Sub test()
HighlightDuplicates Range("A2:A201")
End Sub
Sub HighlightDuplicates(DuplicateRange As Range)
Dim rng As Range, rngF As Range, rngFull As Range, rngLast As Range
Dim lngColorIndex As Long, varColors As Variant
Set rngFull = DuplicateRange
varColors = Array(3, 4, 5, 6, 7, 8, 9)
lngColorIndex = LBound(varColors)
rngFull.Interior.ColorIndex = xlColorIndexNone
Set rngLast = rngFull(rngFull.Rows.Count, rngFull.Columns.Count)
For Each rng In rngFull
If rng.Interior.ColorIndex = xlColorIndexNone Then
If Application.WorksheetFunction.CountIf(rngFull, rng.Value) 1 Then
For Each rngF In rngFull
If rngF.Value = rng.Value Then rngF.Interior.ColorIndex = varColors(lngColorIndex)
Next
lngColorIndex = lngColorIndex + 1
If lngColorIndex UBound(varColors) Then lngColorIndex = LBound(varColors)
End If
End If
Next
End Sub
Cheers,
Rob
irealtymods wrote:
Submitted via EggHeadCafe - Software Developer Portal of Choice
Excel Identifying which formulas are slowing down workbook recalaculation
http://www.eggheadcafe.com/tutorials...aculation.aspx