Change a cell's background color dynamically according to a RGB value stored in other cells
Solution 1
UDF version:
Function myRGB(r, g, b)
Dim clr As Long, src As Range, sht As String, f, v
If IsEmpty(r) Or IsEmpty(g) Or IsEmpty(b) Then
clr = vbWhite
Else
clr = RGB(r, g, b)
End If
Set src = Application.ThisCell
sht = src.Parent.Name
f = "Changeit(""" & sht & """,""" & _
src.Address(False, False) & """," & clr & ")"
src.Parent.Evaluate f
myRGB = ""
End Function
Sub ChangeIt(sht, c, clr As Long)
ThisWorkbook.Sheets(sht).Range(c).Interior.Color = clr
End Sub
Usage (entered in D1):
=myRGB(A1,B1,C1)
Solution 2
In D1 enter:
=A1 & "," & B1 & "," & C1
and in the worksheet code area, enter the following event macro:
Private Sub Worksheet_Calculate()
Range("D1").Interior.Color = RGB(Range("A1"), Range("B1"), Range("C1"))
End Sub
Solution 3
Assuming you would want this to work with the entire columns instead of just row 1, here is the VBA procedure for the worksheet's code module:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count = 1 Then
If .Column < 4 Then
Cells(.Row, 4).Interior.Color = RGB(Cells(.Row, 1), Cells(.Row, 2), Cells(.Row, 3))
End If
End If
End With
End Sub
Note: I do not know what you mean by the following and so have not addressed it: and also, if I place the function in D2, it will select the RGB stored in A2, B2 and C2
.
epaezr
Updated on July 09, 2022Comments
-
epaezr almost 2 years
I'm trying to write a function in Excel that will set the background color of the active cell according to the values stored in other three cells (each of those three cells store a numeric value from 0 to 255, depending on the color R, G or B).
So the A1 cell is 150, the B1 cell is 220 and the C1 cell is 90 (that's RGB(150, 220, 90)). I need that the D1 cell's color is that RGB declared before (some kind of green), and also, if I place the function in D2, it will select the RGB stored in A2, B2 and C2, and so on...
Can this be achieved?
-
Excel Hero over 8 yearsThat does not seem very useful... with hardcoded ranges
-
Gary's Student over 8 years@ExcelHero The code was "designed" with the question in mind.
-
Excel Hero over 8 yearsEven if those three cells are actually all the OP wants, your solution is not a good design. Why run this every single time the worksheet calculates? The task prescribed should be kept to an absolute minimum number of times executed.
-
Scott Craner over 8 yearsNever thought of calling a sub from the function to do the change, NICE.
-
Excel Hero over 8 years@Gary'sStudent My apologies. Reading it back now I see it was quite a bit harsher than I intended.
-
A.S.H over 8 yearsBeautiful. This technique can be used to achieve many other things as well.
-
A.S.H over 8 yearsEH, I guess the answer of Tim addressed the pending issue. A UDF that the user can place anywhere.
-
Excel Hero over 8 years@A.S.H Maybe. Still don't know for sure what OP meant.
-
epaezr over 8 yearsThis is just what I needed!! Thank you Tim!
-
Josh over 5 years@Gary'sStudent, your code works perfectly for cell D1! Is there a way to make this code work in any given cell, drawing the RGB data from any given group of three cells? Thanks
-
Admin over 5 yearsSmall typo code to set color - last value (blue) has a period before "Cells(.Row, 3)" Should be: Cells(.Row, 4).Interior.Color = RGB(Cells(.Row, 1), Cells(.Row, 2), Cells(.Row, 3))
-
Devaski over 2 yearsTIM, User Defined Function, given by you is an useful tool, generated a 3000 pixel RGB values into an image. Thanks.