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.

 

[tomres]

Works perfectly in Excel 2010. Full detailed instructions for a novice, too. Thank you very much Richard.

 

[Lesley8000]

Hi Rich,

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

"font-family:Calibri,Arial; font-size:12pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt;">







"background-color:#cacaca; text-align:center;font-size:8pt;">































































































































































































































[TABLE]
 </td>[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
</tr>[TR]
<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]
<td style="
"font-size:8pt; background-color:#cacaca; text-align:center;">
2</td>[TD="align: center"]61005[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=660066]#660066[/URL] "] [/TD]
[/TR]
[TR]
<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]
<td style="
"font-size:8pt; background-color:#cacaca; text-align:center;">
4</td>[TD="align: center"]61015[/TD]
[TD="align: center"]253[/TD]
[TD="align: center"]205[/TD]
[TD="align: center"]203[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc99]#ffcc99[/URL] "] [/TD]
[/TR]
[TR]
<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]
<td style="
"font-size:8pt; background-color:#cacaca; text-align:center;">
6</td>[TD="align: center"]61049[/TD]
[TD="align: center"]107[/TD]
[TD="align: center"]161[/TD]
[TD="align: center"]104[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=339966]#339966[/URL] "] [/TD]
[/TR]
[TR]
<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]
<td style="
"font-size:8pt; background-color:#cacaca; text-align:center;">
8</td>[TD="align: center"]61022[/TD]
[TD="align: center"]245[/TD]
[TD="align: center"]243[/TD]
[TD="align: center"]213[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] "] [/TD]
[/TR]
[TR]
<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]
<td style="
"font-size:8pt; background-color:#cacaca; text-align:center;">
10</td>[TD="align: center"]61024[/TD]
[TD="align: center"]255[/TD]
[TD="align: center"]174[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff9900]#ff9900[/URL] "] [/TD]
[/TR]
[TR]
<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]
<td style="
"font-size:8pt; background-color:#cacaca; text-align:center;">
12</td>[TD="align: center"]61029[/TD]
[TD="align: center"]106[/TD]
[TD="align: center"]128[/TD]
[TD="align: center"]220[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3366ff]#3366ff[/URL] "] [/TD]
[/TR]
[TR]
<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]
<td style="
"font-size:8pt; background-color:#cacaca; text-align:center;">
14</td>[TD="align: center"]61031[/TD]
[TD="align: center"]214[/TD]
[TD="align: center"]179[/TD]
[TD="align: center"]205[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[/TR]
[TR]
<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]
<td style="
"font-size:8pt; background-color:#cacaca; text-align:center;">
16</td>[TD="align: center"]61035[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=993366]#993366[/URL] "] [/TD]
[/TR]
[TR]
<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]
<td style="
"font-size:8pt; background-color:#cacaca; text-align:center;">
18</td>[TD="align: center"]61039[/TD]
[TD="align: center"]202[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff00ff]#ff00ff[/URL] "] [/TD]
[/TR]
[TR]
<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]
<td style="
"font-size:8pt; background-color:#cacaca; text-align:center;">
20</td>[TD="align: center"]61041[/TD]
[TD="align: center"]117[/TD]
[TD="align: center"]114[/TD]
[TD="align: center"]114[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] "] [/TD]
[/TR]
[TR]
<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]
<td style="
"font-size:8pt; background-color:#cacaca; text-align:center;">
22</td>[TD="align: center"]61043[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]60[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=333333]#333333[/URL] "] [/TD]
[/TR]
[TR]
<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:[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=993366]#993366[/URL] ; font-size:10pt;"> </td>[/TR]
[TR]
<td style="
"font-size:8pt; background-color:#cacaca; text-align:center;">
24</td>[TD="align: center"]61045[/TD]
[TD="align: center"]139[/TD]
[TD="align: center"]203[/TD]
[TD="align: center"]203[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=33cccc]#33cccc[/URL] "] [/TD]
[/TR]
[TR]
<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]
<td style="
"font-size:8pt; background-color:#cacaca; text-align:center;">
26</td>[TD="align: center"]61047[/TD]
[TD="align: center"]191[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]161[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff99]#ffff99[/URL] "] [/TD]
[/TR]
[TR]
<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:[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=339966]#339966[/URL] ; font-size:10pt;"> </td>[/TR]
</tbody>[/TABLE]

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

Best Wishes,
Will

Hi Will,

I have also tried this code and would love to know what YOU did to get it working properly! Please help me out as I am struggling to get this working and it's doing my head in... big time!

 

[Rick Rothstein]

I have also tried this code and would love to know what YOU did to get it working properly! Please help me out as I am struggling to get this working and it's doing my head in... big time!

WmBanx wrote his message more than 7 years ago, so my guess is he won't be answering your question (assuming he would even remember). Richard's code should work straight away for you... did you follow the instructions he gave in the first paragraph of Message #4 ?

 

[Lesley8000]

WmBanx wrote his message more than 7 years ago, so my guess is he won't be answering your question (assuming he would even remember). Richard's code should work straight away for you... did you follow the instructions he gave in the first paragraph of Message #4 ?

Yes I did, to the letter and it doesn't work for me, I have excel 2016, so I'm wondering if the version I have of VB has anything to do with the problem! Is there a formula I can use in place of the VB code to convert RGB to the actual colour I require please?

 

[Rick Rothstein]

Yes I did, to the letter and it doesn't work for me, I have excel 2016, so I'm wondering if the version I have of VB has anything to do with the problem! Is there a formula I can use in place of the VB code to convert RGB to the actual colour I require please?

A formula cannot do anything except return a value to the cell it is placed in... it cannot physically change anything in Excel. I don't have Excel 2016, so I cannot test the code there; however, I just tested it and it works perfectly in Excel 2010. I will mention one thing that would prevent the code from working... if the cells you put the numbers in (Columns A thru C) are formatted as Text, then nothing happens as the numbers in those cells must be real numbers.

 

[Lesley8000]

OK, I have changed those columns accordingly and I'm still having the same problem, a dialogue box is coming up wanting me to name the macro and I have just tried removing the Hex code column so that the formula is no longer present and still having the same problem!

 

[Rick Rothstein]

OK, I have changed those columns accordingly and I'm still having the same problem, a dialogue box is coming up wanting me to name the macro

You should not be getting that dialog box display for event code. The only way I can think that it is getting displayed is if you are pressing ALT+F8 on the keyboard. Are you doing that? If so, why are you doing it? I did think of one other way the code would not be doing anything for you... if you installed it in a worksheet module other than the worksheet module for the worksheet you are inputting your numbers into. The instructions Richard gave in Message #4 were meant for you to right-click specifically the tab where you wanted to input the numbers at, not just any tab.

 

[Lesley8000]

You should not be getting that dialog box display for event code. The only way I can think that it is getting displayed is if you are pressing ALT+F8 on the keyboard. Are you doing that? If so, why are you doing it? I did think of one other way the code would not be doing anything for you... if you installed it in a worksheet module other than the worksheet module for the worksheet you are inputting your numbers into. The instructions Richard gave in Message #4 were meant for you to right-click specifically the tab where you wanted to input the numbers at, not just any tab.

No, I'm not pressing F8 to run the code, I'm pressing F5, is that right? What's this worksheet module thing. Can put all this in instructions for me, so that I can follow along, I am autistic and sometimes have trouble understanding things! I would be grateful if you can post instructions on exactly what I should be doing!

 

[Rick Rothstein]

No, I'm not pressing F8 to run the code, I'm pressing F5, is that right?

No, that is not right. There is nothing for you to run with event code... it runs all by itself when something you do on the worksheet triggers it. There are several events that Excel can respond to. For the code Richard posted, that event is the Change event which react whenever you complete an action in a cell (enter a value, delete a value, edit a value, etc.) The code Richard posted should execute automatically whenever there is a value entered in Columns A, B and C on the same row.

What's this worksheet module thing. Can put all this in instructions for me, so that I can follow along,

There are several code modules available in VBA. I don't know if you are familiar with UserForms (they are like dialog boxes, but you construct them completely by yourself), but it has its own code module where code controlling all aspects of the UserForm go. There is also a code module for those who do Object Oriented coding (Insert/Class Module) on the VB editor's menu bar). The General module (Insert/Module on VB's menu bar) is for macros, UDFs (user defined functions), user created subroutines and functions. The Workbook has it own code module (you can bring it up by double-clicking the word "ThisWorkbook" in the VB editor window labeled "Project-VBAProject"... press CRTL+R if you don't see it) where event code that applies to the entire workbook goes. Finally, each worksheet has its own code mode where event code for that sheet and that sheet only goes. The simple way to bring up a code module for a specific worksheet is to right-click that sheet's tab and select "View Code" from the popup menu that appears (you can also double-click the sheet name in the VB editor window labeled "Project-VBAProject similar to how the code module for the entire workbook is brought up). The important thing for worksheet event code is that you bring up the code module for the worksheet that you want the code to monitor. That is the step I think you may be getting wrong. Let's say the worksheet where you are going to put the RGB numbers at, and where the cell color will be displayed is named "Sheet3". To instal Richard's code for that sheet, you must right-click the tab labeled "Sheet3" and click "View Code" from the popup menu that appears when you do that. Also, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Lesley8000]

OK, well can u please write me a list of instructions starting with #1 , so I can follow them and get this right? I would appreciate that!