Determining RGB values of each item in the Standard Color Palette

[NewOrderFac33]

Good afternoon,

Nice for for somebody for a Friday afternoon!

I know how to use the 56 .ColorIndex values, and how to set an interior or font color to an RGB value, but does anyone know of a quick way to return the RGB value of each element in the Standard Color Palette (Excel 2016) to a cell in a worksheet range ,say A1:A500 ?

Setting the fill colour of each cell to its corresponding RGB value would be pretty cool, too!

Thanks in advance and have a nice weekend

Pete

 

[RoryA]

Do you mean something like this:

Sub colourList()
Dim n As Long
For n = 1 To 56
    With Cells(n, "A")
        .Value2 = ActiveWorkbook.Colors(n)
        .Interior.Color = ActiveWorkbook.Colors(n)
    End With
Next

End Sub

 

[JoeMo]

If you want RGB values for fill color you can use a UDF as in the example below where column B gives the RGB values for the first 10 fill colors produced by Rory's code in column A.

Excel Workbook
	A	B
1	0	R=0, G=0, B=0
2	16777215	R=255, G=255, B=255
3	255	R=255, G=0, B=0
4	65280	R=0, G=255, B=0
5	16711680	R=0, G=0, B=255
6	65535	R=255, G=255, B=0
7	16711935	R=255, G=0, B=255
8	16776960	R=0, G=255, B=255
9	128	R=128, G=0, B=0
10	32768	R=0, G=128, B=0
Sheet5

Function GetRGBFill(rCell) As String
'Returns the RGB values for fill of cell rcell
    Dim c As Long
    Dim R As Long
    Dim G As Long
    Dim b As Long
Application.Volatile
    c = rCell.Interior.Color
    R = c Mod 256
    G = (c \ 256) Mod 256
    b = (c \ 65536) Mod 256
    GetRGBFill = "R=" & R & ", G=" & G & ", B=" & b
End Function

 

[NewOrderFac33]

Thanks for the replies, chaps. What I was after was a way of trapping the RGB values of each of the colors in the hexagonal "Standard Colors" palette, rather than going through them one at a time, coloring a cell and trapping its RGB value using JoeMo's excellent function above.
Cheers
Pete

 

[JoeMo]

Thanks for the replies, chaps. What I was after was a way of trapping the RGB values of each of the colors in the hexagonal "Standard Colors" palette, rather than going through them one at a time, coloring a cell and trapping its RGB value using JoeMo's excellent function above.
Cheers
Pete

Glad we could help - thanks for the reply.

 

[Fluff]

As the values in the standard palette seem to increment by 51, how about

Sub NewOrderFac33()
   Dim r As Long, g As Long, b As Long, x As Long
   For r = 0 To 255 Step 51
      For g = 0 To 255 Step 51
         For b = 0 To 255 Step 51
            x = x + 1
            Cells(x, 1).Interior.Color = RGB(r, g, b)
            Cells(x, 2).Value = "R=" & r & ", G=" & g & ", B=" & b
         Next b
      Next g
   Next r
End Sub

 

[Fluff]

Just realised it's not that simple. The palette only has about 120 colours, not the 216 my code gives.

 

[NewOrderFac33]

Looks like I'll just have to do it manually for each element of the palette, then - not to worry - thanks both for your interest.
Pete