Answered by:
vba Excel 2010, "copy" RGB colour values from cell into variables

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
Question
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
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 

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 
Hi Dwipayan Das, thanks for your reply.
Actually user sets background colour of cell: rightclick 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.

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 
Dwipayan Das,
your decision is ok for cell with onecolour 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).ColorI'm still on the way, so any ideas are strongly appreciated...

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

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?