Dynamically assigning cell RGB background color using contents of 3 other cells.

[WmBanx]

Hi, I am very new to this forum with this being my first posting. Please be gentle with me.

Currently I'm finding the following simple task very difficult to achieve in Excel.

I'm wanting to dynamically set the RGB background color for cells in Column D to that of RGB values held in columns A,B,C. In other words, I am wanting to avoid achieving this by using Conditional Formatting with custom STYLES.

ie. A1=REDval, B1=GREENval, C1=BLUEval then cell backgroud color D1 = RGB(A1, B1, C1)

Example1:

A1=253, B1=205, C1=203
Then background RGB color of cell D1 to be automatically set to RGB(A1, B1, C1) = RGB(253, 205, 203)

Example2:

Background colour of Cell D1 = RGB("Value of A1","Value of B1","Value of C1")
Background colour of Cell D2 = RGB("Value of A2","Value of B2","Value of C2")
..
..
Background colour of Cell D65535 = RGB("Value of A65535","Value of B","Value of C65535")

I've tried looking for an answer but my keyword searches are either weak or inaccurate.

Any help or a solution would be most appreciated.

Will.

 

[Richard Schollar]

Hi Will

What version of Excel are you using? If before xl2007 then you simply won't be able to assign a specific RGB value unless the total number of different RGB values you wish to assign is less than or equal to 56 as this is the maximum in xl2003 and below. You would also need to modify your colour palette dynamically unless you were certain you would never use any other colours than those defined in your 56 colour palette.

 

[WmBanx]

Hi Richard,

Yes, I thought that was the case for pre-Excel2007. I am currently trying to do this in Excel2007 / OpenOffice Calc v3.2.

Do you have any script / code suggestions ? .. I am a relative novice when it comes to macro/VBA coding but the penny does eventually drop.

 

[Richard Schollar]

In the worksheet where you want this colour banding to occur, right-click on the tab name at the bottom of Excel and select View Code. Paste the following into the code window which will open:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range
Dim i As Long

Set rng = Intersect(Target, Range("A:C"))
If Not rng Is Nothing Then
    On Error Resume Next
    For Each cell In Target.Columns(1).Cells
        If Application.CountA(Range("A" & cell.Row & ":C" & cell.Row)) < 3 Or _
            Application.Count(Range("A" & cell.Row & ":C" & cell.Row)) < 3 Then GoTo next_row
        Cells(cell.Row, "D").Interior.Color = _
            RGB(Cells(cell.Row, "A").Value, Cells(cell.Row, "B").Value, Cells(cell.Row, "C").Value)
next_row:
    Next cell
End If
End Sub

Then go back into your sheet and enter some values in columns A:C. You will need a value in each of A:C on that particular row before the colour is applied in column D. If you already have a lot of values in A:C already, then select column A and go Ctrl+C>Ctrl+V to re-enter and thus apply the formatting to column D.

 

[WmBanx]

Hi Rich,

With a quick customisation from your original code, have now got what I require :

<table border="1" cellspacing="0" cellpadding="0" style=
"font-family:Calibri,Arial; font-size:12pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt;">
<colgroup>
<col style="width:30px;">
<col style="width:80px;">
<col style="width:45px;">
<col style="width:45px;">
<col style="width:45px;">
<col style="width:104px;"></colgroup>
<tr style=
"background-color:#cacaca; text-align:center;font-size:8pt;">
<td> </td>
<td>A</td>
<td>B</td>
<td>C</td>
<td>D</td>
<td>E</td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
1</td>
<td style="font-size:10pt; text-align:center;">61002</td>
<td style="font-size:10pt; text-align:center;">245</td>
<td style="font-size:10pt; text-align:center;">238</td>
<td style="font-size:10pt; text-align:center;">242</td>
<td style="font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
2</td>
<td style="font-size:10pt; text-align:center;">61005</td>
<td style="font-size:10pt; text-align:center;">32</td>
<td style="font-size:10pt; text-align:center;">4</td>
<td style="font-size:10pt; text-align:center;">22</td>
<td style="background-color:#660066; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
3</td>
<td style="font-size:10pt; text-align:center;">61011</td>
<td style="font-size:10pt; text-align:center;">150</td>
<td style="font-size:10pt; text-align:center;">150</td>
<td style="font-size:10pt; text-align:center;">149</td>
<td style="background-color:#969696; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
4</td>
<td style="font-size:10pt; text-align:center;">61015</td>
<td style="font-size:10pt; text-align:center;">253</td>
<td style="font-size:10pt; text-align:center;">205</td>
<td style="font-size:10pt; text-align:center;">203</td>
<td style="background-color:#ffcc99; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
5</td>
<td style="font-size:10pt; text-align:center;">61019</td>
<td style="font-size:10pt; text-align:center;">255</td>
<td style="font-size:10pt; text-align:center;">150</td>
<td style="font-size:10pt; text-align:center;">134</td>
<td style="background-color:#ff8080; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
6</td>
<td style="font-size:10pt; text-align:center;">61049</td>
<td style="font-size:10pt; text-align:center;">107</td>
<td style="font-size:10pt; text-align:center;">161</td>
<td style="font-size:10pt; text-align:center;">104</td>
<td style="background-color:#339966; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
7</td>
<td style="font-size:10pt; text-align:center;">61021</td>
<td style="font-size:10pt; text-align:center;">203</td>
<td style="font-size:10pt; text-align:center;">59</td>
<td style="font-size:10pt; text-align:center;">28</td>
<td style="background-color:#ff0000; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
8</td>
<td style="font-size:10pt; text-align:center;">61022</td>
<td style="font-size:10pt; text-align:center;">245</td>
<td style="font-size:10pt; text-align:center;">243</td>
<td style="font-size:10pt; text-align:center;">213</td>
<td style="background-color:#ffffcc; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
9</td>
<td style="font-size:10pt; text-align:center;">61023</td>
<td style="font-size:10pt; text-align:center;">255</td>
<td style="font-size:10pt; text-align:center;">222</td>
<td style="font-size:10pt; text-align:center;">5</td>
<td style="background-color:#ffcc00; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
10</td>
<td style="font-size:10pt; text-align:center;">61024</td>
<td style="font-size:10pt; text-align:center;">255</td>
<td style="font-size:10pt; text-align:center;">174</td>
<td style="font-size:10pt; text-align:center;">0</td>
<td style="background-color:#ff9900; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
11</td>
<td style="font-size:10pt; text-align:center;">61028</td>
<td style="font-size:10pt; text-align:center;">129</td>
<td style="font-size:10pt; text-align:center;">131</td>
<td style="font-size:10pt; text-align:center;">204</td>
<td style="background-color:#666699; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
12</td>
<td style="font-size:10pt; text-align:center;">61029</td>
<td style="font-size:10pt; text-align:center;">106</td>
<td style="font-size:10pt; text-align:center;">128</td>
<td style="font-size:10pt; text-align:center;">220</td>
<td style="background-color:#3366ff; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
13</td>
<td style="font-size:10pt; text-align:center;">61030</td>
<td style="font-size:10pt; text-align:center;">164</td>
<td style="font-size:10pt; text-align:center;">181</td>
<td style="font-size:10pt; text-align:center;">234</td>
<td style="background-color:#9999ff; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
14</td>
<td style="font-size:10pt; text-align:center;">61031</td>
<td style="font-size:10pt; text-align:center;">214</td>
<td style="font-size:10pt; text-align:center;">179</td>
<td style="font-size:10pt; text-align:center;">205</td>
<td style="background-color:#c0c0c0; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
15</td>
<td style="font-size:10pt; text-align:center;">61032</td>
<td style="font-size:10pt; text-align:center;">151</td>
<td style="font-size:10pt; text-align:center;">77</td>
<td style="font-size:10pt; text-align:center;">180</td>
<td style="background-color:#333399; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
16</td>
<td style="font-size:10pt; text-align:center;">61035</td>
<td style="font-size:10pt; text-align:center;">103</td>
<td style="font-size:10pt; text-align:center;">17</td>
<td style="font-size:10pt; text-align:center;">25</td>
<td style="background-color:#993366; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
17</td>
<td style="font-size:10pt; text-align:center;">61036</td>
<td style="font-size:10pt; text-align:center;">139</td>
<td style="font-size:10pt; text-align:center;">54</td>
<td style="font-size:10pt; text-align:center;">52</td>
<td style="background-color:#993300; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
18</td>
<td style="font-size:10pt; text-align:center;">61039</td>
<td style="font-size:10pt; text-align:center;">202</td>
<td style="font-size:10pt; text-align:center;">0</td>
<td style="font-size:10pt; text-align:center;">5</td>
<td style="background-color:#ff00ff; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
19</td>
<td style="font-size:10pt; text-align:center;">61040</td>
<td style="font-size:10pt; text-align:center;">106</td>
<td style="font-size:10pt; text-align:center;">94</td>
<td style="font-size:10pt; text-align:center;">94</td>
<td style="background-color:#ff0000; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
20</td>
<td style="font-size:10pt; text-align:center;">61041</td>
<td style="font-size:10pt; text-align:center;">117</td>
<td style="font-size:10pt; text-align:center;">114</td>
<td style="font-size:10pt; text-align:center;">114</td>
<td style="background-color:#808080; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
21</td>
<td style="font-size:10pt; text-align:center;">61042</td>
<td style="font-size:10pt; text-align:center;">34</td>
<td style="font-size:10pt; text-align:center;">18</td>
<td style="font-size:10pt; text-align:center;">107</td>
<td style="background-color:#000080; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
22</td>
<td style="font-size:10pt; text-align:center;">61043</td>
<td style="font-size:10pt; text-align:center;">56</td>
<td style="font-size:10pt; text-align:center;">38</td>
<td style="font-size:10pt; text-align:center;">60</td>
<td style="background-color:#333333; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
23</td>
<td style="font-size:10pt; text-align:center;">61044</td>
<td style="font-size:10pt; text-align:center;">85</td>
<td style="font-size:10pt; text-align:center;">25</td>
<td style="font-size:10pt; text-align:center;">69</td>
<td style="background-color:#993366; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
24</td>
<td style="font-size:10pt; text-align:center;">61045</td>
<td style="font-size:10pt; text-align:center;">139</td>
<td style="font-size:10pt; text-align:center;">203</td>
<td style="font-size:10pt; text-align:center;">203</td>
<td style="background-color:#33cccc; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
25</td>
<td style="font-size:10pt; text-align:center;">61046</td>
<td style="font-size:10pt; text-align:center;">109</td>
<td style="font-size:10pt; text-align:center;">143</td>
<td style="font-size:10pt; text-align:center;">143</td>
<td style="background-color:#666699; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
26</td>
<td style="font-size:10pt; text-align:center;">61047</td>
<td style="font-size:10pt; text-align:center;">191</td>
<td style="font-size:10pt; text-align:center;">207</td>
<td style="font-size:10pt; text-align:center;">161</td>
<td style="background-color:#ffff99; font-size:10pt;"> </td>
</tr>
<tr style="height:14px ;">
<td style=
"font-size:8pt; background-color:#cacaca; text-align:center;">
27</td>
<td style="font-size:10pt; text-align:center;">61049</td>
<td style="font-size:10pt; text-align:center;">107</td>
<td style="font-size:10pt; text-align:center;">161</td>
<td style="font-size:10pt; text-align:center;">104</td>
<td style="background-color:#339966; font-size:10pt;"> </td>
</tr>
</table>

I thank you kindly Richard for all your help as I no doubt would be still pulling my hair out.

Best Wishes,
Will

 

[keng57]

Hi,
I am also very new to Excel & trying to get color into column D once a value goes into it. I have copied the code into VB code sheet saved it but it doesn't work, as I said I am new to this so maybe you can assist me further.

Thank you.

Ken.

 

[claw20]

I am trying to do a similar thing in excel 2010. I have changed my document to an xlsm so that it allow macros but still am not getting the results I want. Any help and guidance would be greatly appreciated.

 

[Extrude72]

Hi everyone, I'm new here but I would like to ask some clarification in order to work with this kind of Private Sub (as per above example) considering two different column ranges, let's consider i.e.:
1) range between columns F:H (where I wrote respectively the R, G and B values) and column I (that should be filled based the corrispondent RGB values in the same Row)
2) range between columns L:N (where I wrote respectively the R, G and B values) and column O (that should be filled based the corrispondent RGB values in the same Row)
Finally I'm not able to modify the above code considering not only one Range but two different as per above example.... could please anyone help me in this kind of solution (or similar that can us as well by module or other kind of VBA system)?

Thanks in advance hoping that somebody can read my question even if included in an old post
regards

 

[thespecialk]

Hi there. I'd like to follow up with the same question asked a year ago by Extrude72.

I've been able to successfully make the code posted by Richard work for a range. However, I'd like to do multiple ranges using the same code, and I have been unable to figure out how to do that.

Currently have I have columns U, V & W set up to format column L. I'm trying to do the same for X-Y-Z and M, as well as AA, AB, AC for N.

Anyone able to give guidance on how to merge this code for multiple ranges?

 

[thespecialk]

Bump for reply.