Fill 3 columns with RGB value of preceding columns Interior Colour

[nicksoph]

Im trying to fill 3 cells with the rgb values of the interior colour of the preceding cell, eg if cell A1 has a solid blue background then B1 would equal 0, C1=0 and D1=255. Found this code on this site;

Function FillColorRGBArray(Target As Range) As Variant
Dim N As Double, A(3) As Integer
    N = Target.Interior.Color
    A(0) = N Mod 256
    A(1) = Int(N / 256) Mod 256
    A(2) = Int(N / 256 / 256) Mod 256
    FillColorRGBArray = A
End Function

and can use it in the gui by entering the formula =FillColourRGBArray(a1) and pressing ctrl+shift+enter with B2:D2 selected but I cant get the syntax right in VBA and wonder if someone can give an example of how to use this array function. My code currently looks like this;

rng=Range("B1:D1")
rng=FillColorRGBArray("a1")

and produces a compile error - Type mismatch

Any help appreciated

 

[RoryA]

You would need:

    Set Rng = Range("B1:D1")
    Rng.Value = FillColorRGBArray(Range("a1"))