none
vba Excel 2010, "copy" RGB colour values from cell into variables

    Question

  • Hi all,

    My need is to "copy" RGB colour values from cell into variables.

    So user sets desired colours in "Preferences" sheet, cell A1, then I analyze this cell, "copy" RGB colour values from cell into variables and "insert" RGB values into graph. I know how to insert desired colour into graph using vba. I'm having troubles with analyze of cell colour.

    Is it possible at all?

    Looking forward to see your reply. 

    Thanks ahead.

    Excel 2010

    Thursday, February 02, 2012 8:37 AM

Answers

  • UNTESTED

    Not sure but is this what you are trying?

    Sub Sample()
        Dim rng As Range
        Dim Col1 As Long, Col2 As Long
        Dim R1 As Integer, G1 As Integer, B1 As Integer
        Dim R2 As Integer, G2 As Integer, B2 As Integer
        
        Set rng = Range("A1")
        
        Col1 = rng.Interior.Gradient.ColorStops(1).Color
        Col2 = rng.Interior.Gradient.ColorStops(2).Color
        
        R1 = Col1 Mod 256
        G1 = (Col1 \ 256) Mod 256
        B1 = (Col1 \ 256 \ 256) Mod 256
        
        R2 = Col2 Mod 256
        G2 = (Col2 \ 256) Mod 256
        B2 = (Col2 \ 256 \ 256) Mod 256
        
        Sheets("Chart1").SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(R1, G1, B1)
        Sheets("Chart1").SeriesCollection(1).Points(1).Format.Fill.BackColor.RGB = RGB(R2, G2, B2)
    End Sub
    



    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.
    • Marked as answer by Bruce Song Wednesday, February 22, 2012 9:44 AM
    Thursday, February 02, 2012 5:02 PM

All replies

  • Some thing like this where the series colour is the same as the interior fill of a cell.

     

    Sub X()
        Dim objSeries As Series
        Dim rngColours As Range
        Dim lngIndex As Long
        
        Set rngColours = Range("B2:B5")
        lngIndex = 1
        With ActiveChart
            For Each objSeries In .SeriesCollection
                objSeries.Format.Fill.ForeColor.RGB = rngColours.Cells(lngIndex).Interior.Color
                lngIndex = lngIndex + 1
            Next
        End With
        
    End Sub
    
    



    Cheers,

    Andy
    www.andypope.info

    Thursday, February 02, 2012 9:00 AM
  •  Thanks Andy!

    Sorry, I didn't mention it, my chart series uses gradient colour. How do I set BackColor RGB in chart series using the appropriate value of cell?

    Thursday, February 02, 2012 9:14 AM
  •  Are you asking about converting the RBG Color in words say 'RED' --->FF0000 and the FF0000 has to be applied to the graph.

    User Sets A1 Cell as Red

    Code reads the value of A1 Cell as string then using a case statement or Key/Value pair match returns the RGB code for it and apply on the Graph items.

    Is my above understanding correct, so i will write a vba code for you.

     

     


    Dwipayan Das
    Thursday, February 02, 2012 9:47 AM
  • Hi Dwipayan Das, thanks for your reply.

    Actually user sets background colour of cell: right-click on cell -> format cells -> Fill -> Fill effects -> Gradient two colours, Vertical shading styles.

    My need is to "copy" correct cell rgb colours including gradient and "paste" it into chart series.

    Thanks for attention.

     

    Thursday, February 02, 2012 9:54 AM
  • Please try below code which sets the color picking from the active cell

    Sub GetColorFromCell()
        Dim shp As Shape
        Set shp = ActiveSheet.Shapes(1)
        With shp.Fill.ForeColor
           .ObjectThemeColor = ActiveCell.Interior.ThemeColor
           .TintAndShade = ActiveCell.Interior.TintAndShade
        End With
    End Sub
    

    Dwipayan Das
    Thursday, February 02, 2012 10:42 AM
  • Dwipayan Das,

    your decision is ok for cell with one-colour background.

     

    In my case something like that is helpfull:

    i = Cells(2, 1).Interior.Gradient.ColorStops(1).Color
    j = Cells(2, 1).Interior.Gradient.ColorStops(2).Color

     

    I'm still on the way, so any ideas are strongly appreciated...

     

    Thursday, February 02, 2012 12:42 PM
  • UNTESTED

    Not sure but is this what you are trying?

    Sub Sample()
        Dim rng As Range
        Dim Col1 As Long, Col2 As Long
        Dim R1 As Integer, G1 As Integer, B1 As Integer
        Dim R2 As Integer, G2 As Integer, B2 As Integer
        
        Set rng = Range("A1")
        
        Col1 = rng.Interior.Gradient.ColorStops(1).Color
        Col2 = rng.Interior.Gradient.ColorStops(2).Color
        
        R1 = Col1 Mod 256
        G1 = (Col1 \ 256) Mod 256
        B1 = (Col1 \ 256 \ 256) Mod 256
        
        R2 = Col2 Mod 256
        G2 = (Col2 \ 256) Mod 256
        B2 = (Col2 \ 256 \ 256) Mod 256
        
        Sheets("Chart1").SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(R1, G1, B1)
        Sheets("Chart1").SeriesCollection(1).Points(1).Format.Fill.BackColor.RGB = RGB(R2, G2, B2)
    End Sub
    



    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.
    • Marked as answer by Bruce Song Wednesday, February 22, 2012 9:44 AM
    Thursday, February 02, 2012 5:02 PM
  • Hi Andy

    Found this solution on here which seems fairly helpful. I however am trying to apply this in a similar way but using a pie chart which has only 1 series of data (with 12 values). I am trying to get each slice of the pie to show in a different color determined by colors on cells in the underlying spreadsheet. When I use this macro it fills all slices in just the first color and I think this is because I have only 1 series in the chart. How can I get it to use a different color for each slice of the pie?

    Incidentally, I was initially trying to pick up colors from a range of cells which had been conditionally formatted using a 3 color scale but in each case it returned white (ie 255,255,255) which I think is because the conditional formatting is volatile based on the cell's contents and not fixed so trying to get the interior color of the cell didn't work as I had intended. Any way round this?

    Thursday, February 06, 2014 11:30 AM