Using "record macro" to format chart bar colors with chart as a separate worksheet

I have recorder turned on and I go through all my bar colors and format them with the appropriate color and stop the macro.  I then get the error message that "the item with the specified name wasn't found".  My sheet is called "quintile chart", but in the VBA...it says "chart 1".  Any help would be greatly appreciated.
Sub color_quintile_charts()
'
' color_quintile_charts Macro
'

'
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(1).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(2).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(3).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(4).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(5).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(6).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(7).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(8).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(9).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(10).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(11).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(12).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(13).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(14).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(15).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(16).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(17).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(18).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(19).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(20).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(21).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(22).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(23).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(24).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(25).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(26).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(27).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(28).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(29).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(30).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(31).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(32).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(33).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(34).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(35).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(36).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(37).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(38).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(39).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points(40).Select
End Sub

Open in new window

majervisAsked:
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.

jppintoCommented:
Your sheet is called "quintile chart" but your chart object is called "Chart 1". There is a diference between the sheet name and the object (chart) name, it's not the same thing.

When you click on your chart on your sheet, what name does it appear on the formula bar? (take a look at the attached example=.

jppinto


Capture.JPG
0
majervisAuthor Commented:
by design, I have my chart on its own sheet...if that matters.
0
jppintoCommented:
"I then get the error message..." - you get this messagem when you run the macro again or when you stop the macro recording?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jppintoCommented:
I have my chart on its own sheet

You have your chart on a sheet called "quintile chart" but your chart is called "Chart 1"! Your sheet and your chart don't have the same name.
0
Rory ArchibaldCommented:
Welcome to the joys of the 2007 macro recorder, which is broken. Your code does not actually achieve anything at all (it simply selects the points one after the other but doesn't do anything to them!)
If your chart is on its own sheet, you refer to it as Charts("quintile chart")
0
majervisAuthor Commented:
I can't test for a few hours but will that fix my macro...by changing to the above?  Thank you.
0
Rory ArchibaldCommented:
No, because as I said, your code doesn't actually do anything other than select each data point in turn. No formatting is applied at all.
0
majervisAuthor Commented:
Rorya:

This is related to that same issue I was having from last week that I believe you took a look at.  Here is a simple example that will help me with my issue.  I am not trying to solve the pivot table/filtering issue.  I just want a macro that in my simple attached example will color the first bar yellow...the second one red and the third item green.  Any help would be greatly appreciated.  Thank you in advance.
fruit.xlsm
0
Rory ArchibaldCommented:
Here's sample code based on your workbook. You can also use explicit RGB values for the colours if you prefer:
Sub FormatChartPoints()
   Dim cht As Chart
   Dim n As Long
   Set cht = Charts("Chart1")
   With cht.SeriesCollection(1)
      With .Points(1).Format.Fill
         .Visible = msoTrue
         .ForeColor.SchemeColor = 13   'yellow
         .Transparency = 0
         .Solid
      End With
      With .Points(2).Format.Fill
         .Visible = msoTrue
         .ForeColor.SchemeColor = 10   'red
         .Transparency = 0
         .Solid
      End With
      With .Points(3).Format.Fill
         .Visible = msoTrue
         .ForeColor.SchemeColor = 17   'green
         .Transparency = 0
         .Solid
      End With
   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
majervisAuthor Commented:
I would like to use the RGB colors after all.  How would I modify the above example?

Sub FormatChartPoints()
   Dim cht As Chart
   Dim n As Long
   Set cht = Charts("Chart1")
   With cht.SeriesCollection(1)
      With .Points(1).Format.Fill
         .Visible = msoTrue
         .ForeColor.SchemeColor = 13   'yellow
         .Transparency = 0
         .Solid
      End With
      With .Points(2).Format.Fill
         .Visible = msoTrue
         .ForeColor.SchemeColor = 10   'red
         .Transparency = 0
         .Solid
      End With
      With .Points(3).Format.Fill
         .Visible = msoTrue
         .ForeColor.SchemeColor = 17   'green
         .Transparency = 0
         .Solid
      End With
   End With
End Sub
0
Rory ArchibaldCommented:
Instead of
.ForeColor.SchemeColor
Use:
.ForeColor.RGB = RGB(255, 0, 0)
for example.
0
majervisAuthor Commented:
Perfect.  Thank you and have a great day.
0
majervisAuthor Commented:
ugh...I am not good at VBA.  How would I add a border around each data point, black with a default width?  Thank you in advance.
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 Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.