Color cells bsed on table of RGB numbers

[nicksoph]

Hi
Im using Excel 2007 and have a table of 3 colums: Each row gives a colours RGB values in numbers from 1 to 255. I would like the 4th row to display the color based upon the numbers in the first cells.

Any help would be appreciated.

Best wishes
Nic

 

[nicksoph]

Thanks - that has worked perfectly.

If either of you would like a spiraly dot patterned picture (which is the product of this spreadsheet.) Please just shout.

Thanks agiain.
nic

 

[Bill Rockenbach]

VoG,
In xl prior to xl2007 your code does not create true RBG colors as excel picks the closest match from the color palett. Has this changed in xl2007?

Bill

 

[VoG]

Hi Bill.

Yes, XL 2007 renders RGB colors correctly. Here's a screenshot of the results of testing my code:

 

[Bill Rockenbach]

So in xl2007 you would show a gradual chnge in color using the RBG numbers below instead of blocks of colors?

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="BACKGROUND-COLOR: #00ffff"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="BACKGROUND-COLOR: #00ffff"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">40</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="BACKGROUND-COLOR: #00ffff"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">60</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="BACKGROUND-COLOR: #00ffff"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">80</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="BACKGROUND-COLOR: #00ffff"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="BACKGROUND-COLOR: #00ffff"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">120</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="BACKGROUND-COLOR: #ccffff"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">140</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="BACKGROUND-COLOR: #ccffff"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">160</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="BACKGROUND-COLOR: #ccffff"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">180</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="BACKGROUND-COLOR: #ccffff"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">200</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="BACKGROUND-COLOR: #ccffff"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">220</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="BACKGROUND-COLOR: #ccffff"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">240</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD style="TEXT-ALIGN: right">255</TD><TD> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[VoG]

Yes, although Excel Jeanie doesn't display them correctly:

 

[Bill Rockenbach]

Thanks for the leason

 

[wdbrewery]

Assuming that your table of RGB numbers is in columns A to C then the following will color column D accordingly.

First click the officer button

then click Excel Options, check Show Developer tab in the Ribbon and click OK.

Press ALT + F11 to open the Visual Basic Editor, then select Module from the Insert menu. Paste the following into the white space on the right:

Sub ColorMe()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("D" & i)
        .Interior.Color = RGB(.Offset(, -3).Value, .Offset(, -2).Value, .Offset(, -1).Value)
    End With
Next i
End Sub

Press ALT + Q to return to your sheet. On the Developer tab click Macros, click on ColorMe and click the Run button.

Sorry, I have very little skill with code. I would like to use this code but specify the 3 different cells to grab the R G & B number and then specify another cell to change the color. I would really appreciate the help. Thanks!

 

[VoG]

Try like this - change the ranges to suit

Sub ColorMe()
Range("D1").Interior.Color = RGB(Range("A1").Value, Range("B1").Value, Range("C1").Value)
End Sub

 

[wdbrewery]

Try like this - change the ranges to suit

Sub ColorMe()
Range("D1").Interior.Color = RGB(Range("A1").Value, Range("B1").Value, Range("C1").Value)
End Sub

Shows how little I understand; I was trying to make it too difficult. That was easy. Thanks a lot.

 

[alimagator]

Try like this - change the ranges to suit

Sub ColorMe()
Range("D1").Interior.Color = RGB(Range("A1").Value, Range("B1").Value, Range("C1").Value)
End Sub

I'm trying to accomplish this same thing and this old thread is the only thing that semi makes sense to me. I have no background in any of this programming. I can get this code to work, but it only works for cell D1. I've tried modifying the values to make it do the full range of a column, but just can't figure it out. What's the proper way to refer to the entire A/B/C/D columns using this coding method?