View original (French)

Change the color of series on Excel 2007 chart

Solved
linum56 Posted messages 13 Status Member -  
Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   -
Hello,
I have several curves on the same graph. Excel gives me random colors. I would like to customize the graph by choosing specific colors for each curve. Is there anyone who could help me with a macro?
Thank you in advance!

Configuration: Windows 7 / Chrome 47.0.2526.106

2 answers

  1.
    Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 785
     
    You will go faster by changing the color manually:
    right-click on the curve / Format Data Series / Line color

    --
    Best regards
    Patrice
    1
    1. linum56 Posted messages 13 Status Member
       
      Thank you, Patrice33740. I can do that manually as you suggested, but I would like to automate the process with a Macro. Do you have a Macro to propose?
      Thank you in advance.
      0
    2. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 785
       
      It lacks precision, we need to define:
      - the execution mode of the macro (button or event-based) and if event-based, the triggering event.
      - the name of the chart concerned and perhaps the name of the sheet
      - the name of each series concerned and the color you wish to assign to it.
      0
      1. linum56 Posted messages 13 Status Member > Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention  
         
        Thank you for your interest and the prompt response.
        Execution mode: Button
        Chart name: Chart 2
        Sheet name: Data
        Workbook name: TestLJ
        The curves: Values, Average, M-3σ, M-2σ, M-σ; M+σ, M+2σ, M-3σ (7 curves)
        Corresponding colors: blue, red, orange, green, green (again), orange, red
        Thank you again.
        0
  2. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 785
     
    Hello,

    “Values, Average, M-3s, M-2s, M-s; M+s, M+2s, M+3s”:
    That's 8 curves and not 7!!!
    Check the names of the series: they cannot contain special characters!!!
    In the code, I replaced "σ" with "s"
    Here is a code: 
    Option Explicit Private Sub CommandButton1_Click() Dim s As Series For Each s In ActiveSheet.ChartObjects("Graph 2") _ .Chart.SeriesCollection Select Case s.Name Case "Value" s.Border.Color = RGB(0, 0, 0) 'Black Case "Average" s.Border.Color = RGB(0, 0, 255) 'Blue Case "M-3s" s.Border.Color = RGB(255, 0, 0) 'Red Case "M-2s" s.Border.Color = RGB(255, 204, 0) 'Orange Case "M-s" s.Border.Color = RGB(0, 255, 0) 'Green Case "M+s" s.Border.Color = RGB(0, 255, 0) 'Green Case "M+2s" s.Border.Color = RGB(255, 204, 0) 'Orange Case "M+3s" s.Border.Color = RGB(255, 0, 0) 'Red End Select Next s End Sub

    --
    Regards
    Patrice
    0
    1. linum56 Posted messages 13 Status Member
       
      Hello Patrice
      I tried this macro. At first, Excel does not recognize the names of the curves. I modified the macro so that it lists the names of the curves. As a result, it calls them: "Series1", "Series2", etc., up to "Series8". I made sure that Excel enters the loop and exits it. Everything is normal except for the colors of the curves, which do not change. So the problem remains unsolved!
      Thank you for your interest.
      0
    2. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 785
       
      At home it works, give an example of your file on https://www.cjoint.com/
      0
    3. linum56 Posted messages 13 Status Member
       
      Here dear Patrice is the link to retrieve my data file.
      http://www.cjoint.com/c/ELsp7niaOeT
      thank you again.
      0
    4. linum56 Posted messages 13 Status Member
       
      Hello Patrice
      The macro works very well
      there was just a problem with the "space" character after the series names, and the macro started working again after removing these spaces.
      So the problem is solved
      thank you very much
      0
    5. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 785
       
      I just noticed that!!!

      Looking forward to hearing from you again on the Forum

      Best regards
      Patrice
      0