Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Change a cell's background color dynamically according to a RGB value stored in other cells

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?

like image 543
epaezr Avatar asked Oct 16 '15 20:10

epaezr


3 Answers

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)
like image 126
Tim Williams Avatar answered Sep 19 '22 15:09

Tim Williams


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

enter image description here

like image 43
Gary's Student Avatar answered Sep 19 '22 15:09

Gary's Student


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.

like image 27
Excel Hero Avatar answered Sep 18 '22 15:09

Excel Hero