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

change a cells background color dynamically according to a rgb value stored in other cells
excel get cell color value
excel vba change cell color based on value
excel color cell based on hex value
excel find background color of cell
vba get cell color rgb
how do you make a cell a certain color based on value
excel vba cell color rgb

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?

You can use VBA - something like

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

Just pass in the cell value.

How to change background color in Excel based on cell value, How do I automatically color cells in Excel based on value? 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.

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

Background Colors in Excel VBA, How do I change cell color in Excel based on text input? RGB can be also termed as red green and blue, this function is used to get the numerical value of the color value, this function has three components as a named range and they are red, blue and green the other colors are considered as the components of these three different colors in VBA.

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.

Dynamically assigning cell RGB background color using contents of , How do you highlight cells in Excel based on data? It will color a range of your desire (Rc) with data from (Rs). Sub ColorRGB(Rs As Range, Rc As Range) Dim R As Long Dim G As Long Dim B As Long Dim Address(1 To 3) As Long Dim I As Integer: I = 1 For Each cell In Rs.Cells Address(I) = cell.Value I = I + 1 Next R = Address(1) G = Address(2) B = Address(3) Rc.Interior.Color = RGB(R, G, B) End Sub

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

Determining the RGB Value of a Color (Microsoft Excel), How do you fill a cell with color in Excel VBA? How to change a cell's color based on value in Excel dynamically. The background color will change dependent on the cell's value. Task: You have a table or range of data, and you want to change the background color of cells based on cell values. Also, you want the color to change dynamically reflecting the data changes.

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 !

Cells' background color (Color, RGB, Hex), 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. I would like to have the "swatch" column to have it's background color set by the hex (or RGB) value that is the value of another cell. In pseudo code it would look like this: fill color of A2 = Value in G2 Also, if there was a way that I could sort the table entries chromatically based on the fill value, that would be a large help.

Learn Excel from MrExcel, He knows that he can generate a color based upon a numeric RGB value (as to check out the RGB values for a single cell, the easiest way is to follow these steps: To use the macro, simply add a second parameter to the function used in your Read the sColor hex from a cell and set that as the background colour of the  Change Background Color of Cell Range in Excel VBA – Solution(s): We can use Interior.Color OR Interior.ColorIndex properties of a Rage/Cell to change the background colors. Change Background Color of Cell Range in Excel VBA – Examples. The following examples will show you how to change the background or interior color in Excel using VBA.

How to Change Background Color Based on Cell Value in Excel , If you start your macro recorder and set the background color of a cell you to 64.100, which is a calculated number based on the RGB values. Tap Alt+Q to return to your worksheet. Typing numbers between 0-255 in columns A, B & C will show the RGB color as a cell Fill in column D. The numbers have to be between 0 and 255. The macro is set to accept numbers in columns A, B and C and rows 2 through 20.

Color Functions In Excel, Changing background colors in Excel VBA is easy. Use the Interior property to return an Interior object. Then use the ColorIndex property of the Interior object to set the background color of a cell. Conditional formatting based on another cell. There are times when we need to look at the mounds of data on our daily spreadsheets. However, without any design elements or visual cues, the scouring the data can be stressful to the eyes. Fortunately, though, we can change the cell colors based on the cell value in Google Sheets application!

Comments
  • Thanks, this is also very helpful.