Excel: Change background color of a cell to RGB color written in that cell

Question

I have this code which shows rgb color of target cell:

Function getRGB(RefCell)
Dim mystr As String
Application.Volatile
    mystr = Right("000000" & Hex(RefCell.Interior.Color), 6)
    getRGB = Application.Hex2Dec(Right(mystr, 2)) & ", " & _
             Application.Hex2Dec(Mid(mystr, 3, 2)) & ", " & _
             Application.Hex2Dec(Left(mystr, 2))
End Function

I need that this code instead of showing off rgb of other cell, would change background color of its own cell. Maybe anyone know how to do it?

A function used as a UDF can't modify the sheet it's in or generally alter anything in the workbook: it can only return a value. https://support.microsoft.com/en-us/kb/170787 — Tim Williams, Jul 30 '15 at 06:35
@TimWilliams, then maybe you know what would be best way to read rgb code, and apply it on other cell? — ArnoldasM, Jul 30 '15 at 06:37

score 5 · Accepted Answer · edited May 23 '17 at 12:31

The MSDN KB says

A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following: Insert, delete, or format cells on the spreadsheet.

That unfortunately is incorrect!!!

YOU CAN change the color of the cell from where the formula is called. Here is an example. This will change the color of the cell to Red from where the formula is called.

The trick is to pass a blank value to the sub as the first parameter (a in the below case.)

Why does it work?

I don't know! But it works :)

Function SetIt(RefCell)
    RefCell.Parent.Evaluate "getRGB(" & """""" & "," & RefCell.Address(False, False) & ")"

    SetIt = ""
End Function

Sub getRGB(a As String, RefCell As Range)
    RefCell.Interior.ColorIndex = 3 '<~~ Change color to red
End Sub

ScreenShot

EDIT (Credit Where Due): I had seen this thread by Tim Williams long time ago and I had experimented with it and I had achieved lot of other things which that KB article says is not possible.

BTW I played more with it and I was able to make it work without passing a blank string.

Function SetIt(RefCell)
    RefCell.Parent.Evaluate "getRGB(" & RefCell.Address(False, False) & ")" 
    SetIt = ""
End Function

Sub getRGB(RefCell As Range)
    RefCell.Interior.ColorIndex = 3
End Sub

EDIT

Followup from Duplicate question and chat (Below comments)

Paste this in a code module and then in cell P20 paste the formula =setit(P20,N20)

Function SetIt(DestCell As Range, RefCell As Range)
    RefCell.Parent.Evaluate "SetAndGetRGB(" & RefCell.Address(False, False) & _
                                        "," & _
                                        DestCell.Address(False, False) & ")"

    SetIt = ""
End Function

Sub SetAndGetRGB(RefCell As Range, DestCell As Range)
    Dim sRGB As String
    Dim shName As String

    shName = Split(RefCell.Value, "!")(0)
    sRange = Split(RefCell.Value, "!")(1)

    sRGB = Right("000000" & Hex(Sheets(shName).Range(sRange).Interior.Color), 6)

    DestCell.Interior.Color = RGB( _
                                    Application.Hex2Dec(Right(sRGB, 2)), _
                                    Application.Hex2Dec(Mid(sRGB, 3, 2)), _
                                    Application.Hex2Dec(Left(sRGB, 2)) _
                                  )
End Sub

Note: I have not done any error handling. I am sure you can take care of that.

@SidddhartRout, Thank you for you try. Still cant get it to work... Always getting #value error — ArnoldasM, Jul 30 '15 at 13:34
@ArnoldasM: I hope you are pasting the code in a module? I just posted screenshot of a woking example. — Siddharth Rout, Jul 30 '15 at 13:34
@ArnoldasM: I have also attached a sample. You can download from the above answer. You may have to refresh the page to see it — Siddharth Rout, Jul 30 '15 at 13:40
@ArnoldasM: You are getting that error because the cell B40 in `Spalvos` doesn't have an RGB values. Do you want to use the same color as of B40? — Siddharth Rout, Jul 30 '15 at 13:43
@SiddharthRout If you would look into column N it shows where that color is located, column P shows RGB of that color. And if i use your code like this: `=SetIt(H20:J20;P20)` it drops #value error — ArnoldasM, Jul 30 '15 at 13:48
@ArnoldasM: I think I know what exactly are you trying to achieve... one moment — Siddharth Rout, Jul 30 '15 at 13:49
@SiddharthRout Sorry, it works on your sample.... Just my hands are not straight.... Sorry. — ArnoldasM, Jul 30 '15 at 13:52
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/84704/discussion-between-siddharth-rout-and-arnoldasm). — Siddharth Rout, Jul 30 '15 at 13:54

score 0 · Answer 2 · answered Jul 30 '15 at 06:39

0

Since you cannot set the color of a cell using a Function called as a UDF, you would need to use a sub instead.

Pretty simple example:

Function CopyColor(RefCell As Range, DestCell As Range)
    DestCell.Interior.Color  = RefCell.Interior.Color
End Function

answered Jul 30 '15 at 06:39

Tim Williams

154,628
8
97
125

could you explain where it must be entered? As Vba goes to alt+f11 and this one where? – ArnoldasM Jul 30 '15 at 06:49

score 0 · Answer 3 · answered Jul 30 '15 at 09:20

Siddharth's solution looks good. If you wish to have such function across the sheet without entering formula, put this code on it's VBA page. It will check changes in cells every time the content changes and you can use it to change the color if the content corresponds to the color format:

Private Sub Worksheet_Change(ByVal Target As Range)

' Test if a cell contains the proper formatting
' If it does, assign color
Target.Interior.ColorIndex = Target.Value

End Sub

Excel: Change background color of a cell to RGB color written in that cell

3 Answers3

Linked