different color for duplicates!!! how?

I

irealtymods

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
 
R

Rob van Gelder

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
 
M

Mike Hendrickson

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 Su

Sub HighlightDuplicates(DuplicateRange As Range
Dim rng As Range, rngF As Range, rngFull As Range, rngLast As Rang
Dim lngColorIndex As Long, varColors As Varian

Set rngFull = DuplicateRang

varColors = Array(3, 4, 5, 6, 7, 8, 9
lngColorIndex = LBound(varColors

rngFull.Interior.ColorIndex = xlColorIndexNon
Set rngLast = rngFull(rngFull.Rows.Count, rngFull.Columns.Count

For Each rng In rngFul
If rng.Interior.ColorIndex = xlColorIndexNone The
If Application.WorksheetFunction.CountIf(rngFull, rng.Value) > 1 The
For Each rngF In rngFul
If rngF.Value = rng.Value Then rngF.Interior.ColorIndex = varColors(lngColorIndex
Nex
lngColorIndex = lngColorIndex +
If lngColorIndex > UBound(varColors) Then lngColorIndex = LBound(varColors
End I
End I
Nex
End Su

Cheers
Ro

irealtymods wrote:

Previous Posts In This Thread:

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 the
delete it from all rows
i just used this formula, but donno how to make scenario abov
=COUNTIF(L:L;L27)>

thank you

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 Su

Sub HighlightDuplicates(DuplicateRange As Range
Dim rng As Range, rngF As Range, rngFull As Range, rngLast As Rang
Dim lngColorIndex As Long, varColors As Varian

Set rngFull = DuplicateRang

varColors = Array(3, 4, 5, 6, 7, 8, 9
lngColorIndex = LBound(varColors

rngFull.Interior.ColorIndex = xlColorIndexNon
Set rngLast = rngFull(rngFull.Rows.Count, rngFull.Columns.Count

For Each rng In rngFul
If rng.Interior.ColorIndex = xlColorIndexNone The
If Application.WorksheetFunction.CountIf(rngFull, rng.Value) > 1 The
For Each rngF In rngFul
If rngF.Value = rng.Value Then rngF.Interior.ColorIndex = varColors(lngColorIndex
Nex
lngColorIndex = lngColorIndex +
If lngColorIndex > UBound(varColors) Then lngColorIndex = LBound(varColors
End I
End I
Nex
End Su

Cheers
Ro

irealtymods wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Excel Identifying which formulas are slowing down workbook recalaculation
http://www.eggheadcafe.com/tutorial...are-slowing-down-workbook-recalaculation.aspx
 
M

Mike Hendrickson

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 Su

Sub HighlightDuplicates(DuplicateRange As Range
Dim rng As Range, rngF As Range, rngFull As Range, rngLast As Rang
Dim lngColorIndex As Long, varColors As Varian

Set rngFull = DuplicateRang

varColors = Array(3, 4, 5, 6, 7, 8, 9
lngColorIndex = LBound(varColors

rngFull.Interior.ColorIndex = xlColorIndexNon
Set rngLast = rngFull(rngFull.Rows.Count, rngFull.Columns.Count

For Each rng In rngFul
If rng.Interior.ColorIndex = xlColorIndexNone The
If Application.WorksheetFunction.CountIf(rngFull, rng.Value) > 1 The
For Each rngF In rngFul
If rngF.Value = rng.Value Then rngF.Interior.ColorIndex = varColors(lngColorIndex
Nex
lngColorIndex = lngColorIndex +
If lngColorIndex > UBound(varColors) Then lngColorIndex = LBound(varColors
End I
End I
Nex
End Su

Cheers
Ro

irealtymods wrote:

Previous Posts In This Thread:

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 the
delete it from all rows
i just used this formula, but donno how to make scenario abov
=COUNTIF(L:L;L27)>

thank you

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 Su

Sub HighlightDuplicates(DuplicateRange As Range
Dim rng As Range, rngF As Range, rngFull As Range, rngLast As Rang
Dim lngColorIndex As Long, varColors As Varian

Set rngFull = DuplicateRang

varColors = Array(3, 4, 5, 6, 7, 8, 9
lngColorIndex = LBound(varColors

rngFull.Interior.ColorIndex = xlColorIndexNon
Set rngLast = rngFull(rngFull.Rows.Count, rngFull.Columns.Count

For Each rng In rngFul
If rng.Interior.ColorIndex = xlColorIndexNone The
If Application.WorksheetFunction.CountIf(rngFull, rng.Value) > 1 The
For Each rngF In rngFul
If rngF.Value = rng.Value Then rngF.Interior.ColorIndex = varColors(lngColorIndex
Nex
lngColorIndex = lngColorIndex +
If lngColorIndex > UBound(varColors) Then lngColorIndex = LBound(varColors
End I
End I
Nex
End Su

Cheers
Ro

irealtymods wrote:

Highlighting Sets of Duplicates in Excel
Rob,

Can this script be adjusted to work on the entire A column?

thanks, h


Submitted via EggHeadCafe - Software Developer Portal of Choice
Custom Favorites Web Site with MongoDb and NoRM
http://www.eggheadcafe.com/tutorial...favorites-web-site-with-mongodb-and-norm.aspx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Auto Matching Duplicates? 0
Coloring the duplicates with VBA 5
Coloring duplicates based on values in a column. 6
**finding duplicates from multiple columns and highlighting** 1
custom function color 2
Count cells with content in different color background 2
Cecking for duplicates 8
finding duplicates and deleting based on another column 3

Top