CODEWITHSUNDEEP
excelvbargb
Internet man
Internet man

Reputation: 1155

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

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?

Upvotes: 41

Views: 307806

Answers (5)

Amrit Pal Singh
Amrit Pal Singh

Reputation: 11

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 !

Upvotes: 1

Ben Aveling
Ben Aveling

Reputation: 914

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().

Upvotes: 0

Michel de Ruiter
Michel de Ruiter

Reputation: 7954

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 < 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.

Upvotes: 4

Oorang
Oorang

Reputation: 6770

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

Upvotes: 18

Galwegian
Galwegian

Reputation: 42227

You can use VBA - something like 

Range("A1:A6").Interior.Color = RGB(127,187,199)

Just pass in the cell value.

Upvotes: 67

Related Questions