Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming colors on a pie chart.
Hello there,
I was wondering if someone could help me do a little trick. I have a pie-chart on one sheet and I want the colors of the pie to match the color of a cell on a differnt sheet. That is if cell a1 is green, I want the first slice of the pie to be green. Is this possible? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming colors on a pie chart.
Hannes,
I kind of do this for a line chart and have provided the VBA that I use below... Sub UpdateGraph(p_Sheet As String, p_Graph As String, p_PlotArea As String, p_PlotSeries As String '-----------------------------------------------------------------------------------' ' This routine will update line graph plot area dynamically and format lines and ' ' legend to pre-defined settings. '-----------------------------------------------------------------------------------' Dim Chart As Chart Dim i As Integer Dim Index As Integer Set Chart = ThisWorkbook.Worksheets(p_Sheet).ChartObjects(p_Gr aph).Chart Chart.SetSourceData Source:=Range(p_PlotArea), PlotBy:=xlRows For i = 1 To Range(p_PlotSeries).Count Select Case i Case 1 Index = 3 'Goal - Red Case 2 Index = 4 'Series 2 - Color Case 3 Index = 5 'Series 3 - Color Case 4 Index = 28 'Series 4 - Color Case 5 Index = 7 'Series 5 - Color Case 6 Index = 8 'Series 6 - Color Case 7 Index = 10 'Series 7 - Color Case 8 Index = 15 'Series 8 - Color Case 9 Index = 17 'Series 9 - Color Case 10 Index = 38 'Series 10 - Color Case 11 Index = 45 'Series 11 - Color End Select With Chart.Legend.LegendEntries(i).LegendKey .Border.ColorIndex = Index .Border.Weight = xlMedium .MarkerStyle = xlNone End With Next i Set Chart = Nothing End Sub I think you can reference the color index on a cell pretty easily. HTH, Dean. "Hannes" wrote: Hello there, I was wondering if someone could help me do a little trick. I have a pie-chart on one sheet and I want the colors of the pie to match the color of a cell on a differnt sheet. That is if cell a1 is green, I want the first slice of the pie to be green. Is this possible? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming colors on a pie chart.
I was wondering if someone could help me do a little trick.
I have a pie-chart on one sheet and I want the colors of the pie to match the color of a cell on a differnt sheet. That is if cell a1 is green, I want the first slice of the pie to be green. Maybe something like this: Sub test() Dim cx As Long, n As Long Dim pnt As Point, ch As Chart, rng As Range Set rng = Worksheets("Sheet1").Range("A1:A10") Set ch = ActiveChart If ch Is Nothing Then MsgBox "No chart selected" Else For Each pnt In ch.SeriesCollection(1).Points n = n + 1 cx = rng(n).Interior.ColorIndex If cx < 0 Then cx = xlAutomatic pnt.Interior.ColorIndex = cx Next End If End Sub Fill format enough colours in Sheet1!A1:A10 for the number of slices (points) your pie chart is likely to have. You will need to run again to update if you change colours in A1:A10. If you know the name and location of your chart it's not necessary to select it, eg for a chart on a worksheet: Set ch = Worksheets("Sheet1").ChartObjects("Chart 1").Chart Regards, Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart with VB programming? | Charts and Charting in Excel | |||
Change tab colors via programming | New Users to Excel | |||
Help Programming an XY scatter Chart | Charts and Charting in Excel | |||
Programming Colors | Excel Programming | |||
Pie chart programming | Excel Programming |