Using Colour Codes in an array

[Mike UK]

Hi,
I have a table that stores the field name in column 1 then the colour code in column 2 (see below) in the VBA code I am trying to use the Colour code. If I use it as a number it works but I can't work out the correct number for the RGB codes I have. How do I pass the array variable?

[TABLE="width: 287"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Column 1 [/TD]
[TD]Column 2 [/TD]
[TD] RGB Code[/TD]
[/TR]
[TR]
[TD]Coxes Apples[/TD]
[TD]16225862 [/TD]
[TD]247,150,70[/TD]
[/TR]
[TR]
[TD]Bramley Apples[/TD]
[TD]16225862[/TD]
[TD]247,150,70[/TD]
[/TR]
[TR]
[TD]Commice Pears[/TD]
[TD]1515077[/TD]
[TD]23,30,69[/TD]
[/TR]
[TR]
[TD]Conference Pears[/TD]
[TD]1515077[/TD]
[TD]23,30,69[/TD]
[/TR]
</tbody>[/TABLE]

The Array of Column 1 and Column 2 is called arrAssetClass

Range("B" & Rowcount).Interior.Color = arrAssetClass(AssetClassRow, 2)

Any ideas how I can get the correct number code or pass the RGB or Hex code?
Thanks,
Mike

 

[Norie]

Mike

If the values in column 2 are valid colour codes then they should work.

If you wanted to use the string in the RGB code column you could split it and try something like this.

arrRGB = Split(arrAssetClass(AssetClassRow,3), ",")

Range("B" & Rowcount).Interior.Color = RGB(arrGRB(0),arrGRB(1),arrGRB(2))

 

[Mike UK]

Mike

If the values in column 2 are valid colour codes then they should work.

If you wanted to use the string in the RGB code column you could split it and try something like this.

arrRGB = Split(arrAssetClass(AssetClassRow,3), ",")

Range("B" & Rowcount).Interior.Color = RGB(arrGRB(0),arrGRB(1),arrGRB(2))

Thanks for your help and reply - Column 2 are valid colour codes but I can't find the correct number that corresponds to the RGB code in Column 3. Any ideas how I can find the correct numerical code?

 

[steve the fish]

If i colour some cells using 16225862 and RGB(247,150,70) they arent the same colour. RGB(70,150,247) is though.

 

[steve the fish]

Heres a method once you have sorted out the order issue:

With Sheets("Sheet1")
    lr = .Cells(.Rows.Count, "C").End(xlUp).Row
    arr = .Range("C2:D" & lr)
    For i = LBound(arr) To UBound(arr)
        R = Split(arr(i, 1), ",")(0)
        G = Split(arr(i, 1), ",")(1)
        B = Split(arr(i, 1), ",")(2)
        arr(i, 2) = Int("&H" & Hex(RGB(R, G, B)))
    Next
    .Range("D2:D" & lr) = Application.Index(arr, 0, 2)
End With

This places the number in column D based on the RGB in column C. Is that what you want?

 

[Mike UK]

Thanks Norie - used this code and worked perfectly. Also very simple and only a couple of lines of code..... Many thanks for your help.

 

[Mike UK]

Steve the fish - Thanks for your time and help with my query.