Assigning colors to legend entries with VBA

I have a pie chart with 10 legend entries and I want to assign custom colors to them. The legend names are in Column H, and the colors are in Column A. I thought that this code might work but it bugs on line 8: [Method "Fill" of object 'ChartObject" failed]. How do I fix that? And once that's fixed ,what would be the proper syntax for line 11?

Thanks,
John

Sub LegendColors()
Dim LKP_Cel As Range
For i = 1 To 10
    ActiveSheet.ChartObjects("Chart 6").Activate
    ActiveChart.Legend.Select
    ActiveChart.Legend.LegendEntries(i).Select
    Set LKP_Cel = Cells(i + 8, 8)
    With Selection.Format.Fill
        .Visible = msoTrue
        '.ForeColor.RGB = RGB(128, 0, 128)
        .ForeColor = LKP_Cel.Offset(0, -7).Interior.Color
        .Transparency = 0
        .Solid
    End With
Next i
End Sub

Open in new window

LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John CarneyReliability Business Tools Analyst IIAuthor Commented:
I've come up with a better way to do this. I have the RGB values of the fore and back colors for each data point spread over 6 columns, Q:V, and this code goes through 10 rows, one per data point, and sets the colors quite well except I have to run it twice because the first time through each piece of the pie gets a back color of white. Run it again and it produces the correct gradient.

What do you see that's causing that, and how do I fix it?

Sub ColorSlices()
Dim r1 As Long, g1 As Long, b1 As Long, _
    r2 As Long, g2 As Long, b2 As Long
ActiveSheet.ChartObjects("Chart 6").Activate
For i = 1 To 10
r1 = Cells(i + 26, 17)
g1 = Cells(i + 26, 18)
b1 = Cells(i + 26, 19)
r2 = Cells(i + 26, 20)
g2 = Cells(i + 26, 21)
b2 = Cells(i + 26, 22)
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(i).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(r1, g1, b1)
        .BackColor.RGB = RGB(r2, g2, b2)
        .TwoColorGradient msoGradientHorizontal, 1
    End With
Next i
End Sub

Open in new window

As an example, here's what I have in Row 26. It should be producing a two-tone bluish gradient first time through, I would think.
Cells(i + 26, 17) = 59
Cells(i + 26, 18) = 117
Cells(i + 26, 19) = 117
Cells(i + 26, 20) = 166
Cells(i + 26, 21) = 216
Cells(i + 26, 22) = 110

Open in new window

0
byundtMechanical EngineerCommented:
John,
Could you please post a workbook so I can test your code with the colors you want to use?
Brad
0
byundtMechanical EngineerCommented:
I moved the statement about the two color gradient before the statements setting the forecolor and backcolor. I think that will fix your problem.

I also got rid of all the .Select and .Activate statements...
Sub ColorSlices()
Dim r1 As Long, g1 As Long, b1 As Long, _
    r2 As Long, g2 As Long, b2 As Long
Dim i As Long
With ActiveSheet.ChartObjects("Chart 6").Chart.SeriesCollection(1)
    For i = 1 To 10
        'Cells(i + 26, 17) = 59
        'Cells(i + 26, 18) = 117
        'Cells(i + 26, 19) = 117
        'Cells(i + 26, 20) = 166
        'Cells(i + 26, 21) = 216
        'Cells(i + 26, 22) = 110
        
        r1 = Cells(i + 26, 17)
        g1 = Cells(i + 26, 18)
        b1 = Cells(i + 26, 19)
        r2 = Cells(i + 26, 20)
        g2 = Cells(i + 26, 21)
        b2 = Cells(i + 26, 22)
        With .Points(i).Format.Fill
            .Visible = msoTrue
            .TwoColorGradient msoGradientHorizontal, 1
            .ForeColor.RGB = RGB(r1, g1, b1)
            .BackColor.RGB = RGB(r2, g2, b2)
        End With
    Next i
End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John CarneyReliability Business Tools Analyst IIAuthor Commented:
As always ... thanks, Brad. And you didn't even chastise me for the unnecessary selecting! Out of curiosity, what's the meaning of the commented out lines?

Thanks,
John
0
byundtMechanical EngineerCommented:
John,
You didn't post a sample workbook, and I needed to test the code somehow. So I made every pie slice look like the green to blue gradient that you specified on row 26 for one of your slices.

After debugging, I commented out the statements assigning RGB color components to the cells, and posted in entirety. By doing so, I could easily get back into code tweaking mode if you came back with one or more additional requests.

Brad
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.