Set background colour of cell to RGB value of data in cell

Question

I have a column containing RGB values  e g    127 187 199 67 22 94   In Excel  is there any way I can use this to set the background colour of the cell

User · Answer

Sub AddColor       For Each cell In Selection         R   Round cell Value          G   Round cell Offset 0  1  Value          B   Round cell Offset 0  2  Value          Cells cell Row  1  Resize 1  4  Interior Color   RGB R  G  B      Next cell End Sub   Assuming that there are 3 columns R  G and B  in this order   Select first column ie R  press alt F11 and run the above code  We have to select the first column  containing R or red values  and run the code every time we change the values to reflect the changes    I hope this simpler code helps

User · Answer

Cells cannot be changed from within a VBA function used as a worksheet formula  Except via this workaround     Put this function into a new module   Function SetRGB x As Range  R As Byte  G As Byte  B As Byte    On Error Resume Next   x Interior Color   RGB R  G  B    x Font Color   IIf 0 299   R   0 587   G   0 114   B  lt  128  vbWhite  vbBlack  End Function   Then use this formula in your sheet  for example in cell D2    HYPERLINK SetRGB D2 A2 B2 C2   HOVER      Once you hover the mouse over the cell  try it    the background color updates to the RGB taken from cells A2 to C2  The font color is a contrasting white or black

User · Answer

To color each cell based on its current integer value  the following should work  if you have a recent version of Excel   Older versions don t handle rgb as well   Sub Colourise       Colourise Macro     Colours all selected cells  based on their current integer rgb value   For e g   it s a bit backward from what you might expect    255    ff0000   red   256 255    00ff00   green   256 256 255  0000ff   blue   255   256 256 255  ff00ff   magenta   and so on        Keyboard Shortcut  Ctrl Shift C  or whatever you want to set it to      For Each cell In Selection     If WorksheetFunction IsNumber cell  Then       cell Interior Color   cell Value     End If   Next cell End Sub   If instead of a number you have a string then you can split the string into three numbers and combine them using rgb

User · Answer

You can use VBA - something like   Range  A1 A6   Interior Color   RGB 127 187 199    Just pass in the cell value

User · Answer

Setting the Color property alone will guarantee an exact match  Excel 2003 can only handle 56 colors at once  The good news is that you can assign any rgb value at all to those 56 slots  which are called ColorIndexs   When you set a cell s color using the Color property this causes Excel to use the nearest  ColorIndex   Example  Setting a cell to RGB 10 20 50  or 3281930  will actually cause it to be set to color index 56 which is 51 51 51  or 3355443     If you want to be assured you got an exact match  you need to change a ColorIndex to the RGB value you want and then change the Cell s ColorIndex to said value  However you should be aware that by changing the value of a color index you change the color of all cells already using that color within the workbook  To give an example  Red is ColorIndex 3  So any cell you made Red you actually made ColorIndex 3  And if you redefine ColorIndex 3 to be say  purple  then your cell will indeed be made purple  but all other red cells in the workbook will also be changed to purple    There are several strategies to deal with this  One way is to choose an index not yet in use  or just one that you think will not be likely to be used  Another way is to change the RGB value of the nearest ColorIndex so your change will be subtle  The code I have posted below takes this approach  Taking advantage of the knowledge that the nearest ColorIndex is assigned  it assigns the RGB value directly to the cell  thereby yielding the nearest color  and then assigns the RGB value to that index    Sub Example       Dim lngColor As Long     lngColor   RGB 10  20  50      With Range  A1   Interior          Color   lngColor         ActiveWorkbook Colors  ColorIndex    lngColor     End With End Sub

[excel] Set background colour of cell to RGB value of data in cell

Examples related to excel

Examples related to vba

Examples related to rgb