Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
different color for duplicates!!! how?
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: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting Sets of Duplicates in Excel
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting Sets of Duplicates in Excel
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: On Friday, July 30, 2010 3:20 PM Mike Hendrickson 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/tutorials...-and-norm.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlight Duplicates with different Color | Excel Discussion (Misc queries) | |||
Color duplicates | Excel Discussion (Misc queries) | |||
Need to Color Record in Duplicates | Excel Worksheet Functions | |||
Color Code Duplicates | Excel Programming | |||
Can MS EXCEL remove duplicates and separate by color coded items ? | Excel Programming |