Solved

Identifying pairs of number groups with colours

Posted on 2013-02-04
4
228 Views
Last Modified: 2013-02-04
MartinLiss very kindly gave me this code to help me with my project. (i dont know if I can contact that person directly on here for more help)

The code identifies similar pairs of 'combinations of three numbers' and highlights them in yellow.

This might be asking a lot, but is it possible to have the code do the same function, but highlight each new found pair of similar combinations in a different colour ?
3group.xls
0
Comment
Question by:MichaelGlancy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38852590
I'm here and I'll get back to you with the code.
0
 
LVL 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 38852684
Option Explicit
Private Type Sets
    strAddr As String
    strCells As String
End Type

Sub IdentifyDuplicates()

Dim lngLastRow As Long
Dim lngLastColumn As Long
Dim lngRow As Long
Dim lngCol As Long
Dim DupeSets() As Sets
Dim strSet As String
Dim lngFind As Long
Dim bFound As Boolean
Dim lngColor As Long
Dim R As Integer
Dim G As Integer
Dim B As Integer

ReDim DupeSets(0)
lngLastRow = Range("A65536").End(xlUp).Row
lngLastColumn = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column

Randomize

For lngRow = 3 To lngLastRow
    If Cells(lngRow, 1) <> "" Then
        For lngCol = 9 To lngLastColumn Step 4
            strSet = Cells(lngRow, lngCol) & Cells(lngRow, lngCol + 1) & Cells(lngRow, lngCol + 2)
            bFound = False
            For lngFind = 0 To UBound(DupeSets)
                If strSet = DupeSets(lngFind).strCells Then
                    bFound = True
                    Exit For
                End If
            Next
            If bFound Then
                GetNextColor R, G, B
                Range(DupeSets(lngFind).strAddr).Interior.Color = RGB(R, G, B)
                Range(DupeSets(lngFind).strAddr).Offset(0, 1).Interior.Color = RGB(R, G, B)
                Range(DupeSets(lngFind).strAddr).Offset(0, 2).Interior.Color = RGB(R, G, B)
                Range(Cells(lngRow, lngCol), Cells(lngRow, lngCol + 2)).Interior.Color = RGB(R, G, B)
            Else
                DupeSets(UBound(DupeSets)).strCells = strSet
                DupeSets(UBound(DupeSets)).strAddr = Cells(lngRow, lngCol).Address
                ReDim Preserve DupeSets(UBound(DupeSets) + 1)
            End If

        Next
    End If
Next
End Sub
Sub GetNextColor(R As Integer, G As Integer, B As Integer)
    
    R = Int((256) * Rnd)
    G = Int((256) * Rnd)
    B = Int((256) * Rnd)
    
End Sub

Open in new window

0
 

Author Closing Comment

by:MichaelGlancy
ID: 38853039
Great, works fantastic

I now have more mechanical calculations to make, but if I could ask you more that would be great
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38853066
Ask away. Post the link to the new question here, although I'll probably see it.

In any case you're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question