Change a cell's background color dynamically according to a RGB value stored in other cells
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)
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