Macro to put the RGB number of the fill colour in cells next to that colour

[tonywatsonhelp]

Hi Everyone,
I've been setting up a colour sheet in my template document that I think will be quite handy and at the moment I'm manully put the RGB colours next to the fill colour so i can ref it anytime i need to,
However it would be so much better if I could have it auto fill.

So i need a macro that does this:

When I select a cell and fill the colour, whatever colour I choose the RGB is placed in the cells next to it like this (Let say the RGB was 100,50,230)
Offset 1 column = R (100)
Offset 1 column = G (50)
Offset 1 column = B (230)
Offset 1 column = RGB (100,50,230)

please help if you can
Thanks
Tony

 

[Georgiboy]

I don't think there is an event that captures when a cell's colour is changed, the below will give you the RGB for the active cell:

Sub test()
    Dim rCell As Range
    Dim colorVal As Variant
    Dim RGBcol As Variant
   
    Set rCell = ActiveCell
    colorVal = rCell.Interior.Color
    RGBcol = Split((colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536), ",")
   
    rCell.Offset(, 1) = RGBcol(0)
    rCell.Offset(, 2) = RGBcol(1)
    rCell.Offset(, 3) = RGBcol(2)
    rCell.Offset(, 4) = "RGB(" & Join(RGBcol, ",") & ")"
End Sub

Or to apply to a range of cells:

Sub test()
    Dim rCell As Range
    Dim colorVal As Variant
    Dim RGBcol As Variant
    
    For Each rCell In Range("A2:A10").Cells
        With rCell
            colorVal = .Interior.Color
            RGBcol = Split((colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536), ",")
            .Offset(, 1) = RGBcol(0)
            .Offset(, 2) = RGBcol(1)
            .Offset(, 3) = RGBcol(2)
            .Offset(, 4) = "RGB(" & Join(RGBcol, ",") & ")"
        End With
    Next rCell
End Sub

 

[tonywatsonhelp]

Thanks very much this is perfect