Dynamic Arrays and sorting

[haleybusche]

I am not great with excel macros, so what I think I need is a dynamic array.

I have created a function that assigns the color of a cell to a number, and I need to store that number somewhere so that I can sort by it later.

The number of cells with colors that need to be assigned a number and stored, is dynamic.

Below is the function that assigns the color of the cell to a number

Function GetColor(rngIndex As Range, rngSource As Range) As Long
    Dim lngColor As Long
    Dim J As Integer

    Application.Volatile
    lngColor = rngSource.Interior.ColorIndex

    GetColor = 99       'Set to default color
    For J = 1 To rngIndex.Count
        If rngIndex(J).Interior.ColorIndex = lngColor Then
            GetColor = J
        End If
    Next J
End Function

I need help with code that will store that number. The color of the cell can also change, so I need the number to be updated when the color changes and be restored.

I also need help with the code that will then allow me to sort those numbers.

I know this is a little different, so if further discussion is necessary please feel free.

Thanks

Haley

 

[Gary McMaster]

A dictionary might do the trick. You could use the cell address as the key and store the color as the associated data. Here's some info from Microsoft on how to use the dictionary:

http://support.microsoft.com/kb/246067

Hope it helps.

Gary

 

[Gary McMaster]

Here's a sample. The Microsoft article (above) gives some tips on how to sort the dictionary by key or by data.

Hope it helps.

Gary

Public Sub Test()

'Requires reference to Microsoft Scripting Runtime scrrun.dll
Dim oDict As Scripting.Dictionary 'Perhaps best Declared as Module Public

Set oDict = New Scripting.Dictionary

Dim oCell As Range
Dim oColorTest As Range
Dim vNode As Variant

Set oColorTest = ActiveSheet.Range("A1:A16")

'Fill range with random colors
For Each oCell In oColorTest
    oCell.Interior.ColorIndex = Int((56 * Rnd) + 1)
Next oCell

'Load dictionary: Cell address = key, Data = Color number
For Each oCell In oColorTest
    If oDict.Exists(oCell.Address) Then ' If key (cell address) exists, update color number
        oDict(oCell.Address) = oCell.Interior.ColorIndex
    Else
        oDict.Add oCell.Address, oCell.Interior.ColorIndex 'Add new key & color number
    End If
Next oCell

'Print dictionary contents
For Each vNode In oDict
    Debug.Print vNode & vbTab & oDict(vNode)
Next vNode

'Change a few colors and update the dictionary contents
ActiveSheet.Range("$A$7").Interior.ColorIndex = 3
oDict("$A$7") = 3
ActiveSheet.Range("$A$8").Interior.ColorIndex = 3
oDict("$A$8") = 3
ActiveSheet.Range("$A$9").Interior.ColorIndex = 3
oDict("$A$9") = 3

'Print dictionary contents
For Each vNode In oDict
    Debug.Print vNode & vbTab & oDict(vNode)
Next vNode

End Sub