CODEWITHSUNDEEP
vbaexcelexcel-2010
ArnoldasM
ArnoldasM

Reputation: 206

Excel: Get background color of cell using VBA

I need to get RGB value of cell AU5 (which is filled with black color), but my function shows RGB value of G53. And I just don't know, how to pass the value of cell to function....

enter image description hereenter image description here

And here is a function in VBA:

enter image description here

Maybe anyone could help to fix it?

Upvotes: 1

Views: 14402

Answers (2)

zedfoxus
zedfoxus

Reputation: 37029

Making the function a little easier for myself - that might also help you:

Function getRGB(RefCell As String) As String

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

End Function

In Excel, do this:

=getRGB("AU5")

' Result: 0, 0, 0

Upvotes: 2

BruceWayne
BruceWayne

Reputation: 23283

Just wrap indirect() around your cell ref. So, in G54, use =getRGB(Indirect(G53)).

Without indirect(), the getRGB() function looks to the cell you pointed to, in this case G53. Since you want the formula to look to see what's in G53, and use that reference, just add indirect().

Upvotes: 6

Related Questions