none
change colors of columns on horizonal axis RRS feed

  • Question

  • Hello,

    I don't work with Excel a lot, usually when I get a request for something a couple times a year.

    Anything is possible, right?

    I've automated the creation of a pivot table and then the addition of the pivot chart.

    The problem I have is, I can't figure out how to change the column colors on the horizontal axis. Simple I know...

    I've been playing with properties in Excel to see where I'm missing code, but I can't make it work manually.

    Excel.Shape myChart = detailWS.Shapes.AddChart();


    closl

    Wednesday, February 18, 2015 7:53 PM

Answers

  • Hello,

    I don't work with Excel a lot, usually when I get a request for something a couple times a year.

    Anything is possible, right?

    I've automated the creation of a pivot table and then the addition of the pivot chart.

    The problem I have is, I can't figure out how to change the column colors on the horizontal axis. Simple I know...

    I've been playing with properties in Excel to see where I'm missing code, but I can't make it work manually.

    Excel.Shape myChart = detailWS.Shapes.AddChart();


    closl

    Excel's macro recorder is your friend here. This is what I get:

    ActiveChart.FullSeriesCollection(1).Points(4).Select
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 0)
            .Transparency = 0
            .Solid
        End 

    Wednesday, February 18, 2015 8:36 PM
  • Hi colsl,
    >>My question would be, are there layers of "series"?  I tried to do in c# what you did above, but had a problem figuring it out.<<

    What did you mean that layers of "series"? And what's the problem when you translate the VBA code to C#?

    Here is a sample that change the color of series point in C# for your reference, the chart is on the sheet1:

     string fileName = @"C:\Users\UserName\Desktop\Book1.xlsx";
                Application excelApp = new Application();
                excelApp.Visible = true;
                Workbook aWorkbook = excelApp.Workbooks.Open(fileName);
              
                Chart aChart = aWorkbook.Worksheets["Sheet1"].ChartObjects[1].Chart;
              
                aChart.FullSeriesCollection(1).Points[4].Select();
                Microsoft.Office.Interop.Excel.FillFormat fill = excelApp.Selection.Format.Fill;
                fill.Visible = MsoTriState.msoTrue;
                Color color = Color.FromArgb(0,255,255);
    
                fill.ForeColor.RGB = color.ToArgb();
                fill.Transparency = 0;
                fill.Solid();
     

    Also as John motioned, record macro is a good way for us learning Excel object model.

    Hope it is helpful.

    Regards & Fei



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 19, 2015 3:31 AM
    Moderator

All replies

  • Hello,

    I don't work with Excel a lot, usually when I get a request for something a couple times a year.

    Anything is possible, right?

    I've automated the creation of a pivot table and then the addition of the pivot chart.

    The problem I have is, I can't figure out how to change the column colors on the horizontal axis. Simple I know...

    I've been playing with properties in Excel to see where I'm missing code, but I can't make it work manually.

    Excel.Shape myChart = detailWS.Shapes.AddChart();


    closl

    Excel's macro recorder is your friend here. This is what I get:

    ActiveChart.FullSeriesCollection(1).Points(4).Select
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 0)
            .Transparency = 0
            .Solid
        End 

    Wednesday, February 18, 2015 8:36 PM
  • This is the full method. First a chart is created that contains all the series.

    Then, then a new worksheet is created and the chart on the new worksheet contains only one of the series from the original chart. That is what the picture above displays.

    My question would be, are there layers of "series"? I tried to do in c# what you did above, but had a problem figuring it out.

    Excel.Range srcrange = ws.UsedRange; srcrange.Copy(Type.Missing); //opening of the second worksheet and pasting Excel.Range destrange = newSheet.Cells[1, 1]; destrange.Select(); newSheet.Paste(Type.Missing, Type.Missing); Excel.Shape myChart = newSheet.Shapes.AddChart(); Excel.SeriesCollection seriesCollection = myChart.Chart.SeriesCollection(); int index = 1; myChart.Chart.SetSourceData(newSheet.UsedRange, Type.Missing); myChart.Select(); myChart.Height = 450; myChart.Width = 1500; //myChart.Name = sheetNumber.ToString(); Excel.Series chartCategory = null; while(seriesCollection.Count > 1) {     chartCategory = seriesCollection.Item(index);                    if (chartCategory.Name.Contains(name))     {         chartCategory.IsFiltered = false;         index = 2;     }     else     {         chartCategory.IsFiltered = true;     } } if (chartCategory != nullMarshal.ReleaseComObject(chartCategory);


    closl

    Wednesday, February 18, 2015 9:19 PM
  • Hi colsl,
    >>My question would be, are there layers of "series"?  I tried to do in c# what you did above, but had a problem figuring it out.<<

    What did you mean that layers of "series"? And what's the problem when you translate the VBA code to C#?

    Here is a sample that change the color of series point in C# for your reference, the chart is on the sheet1:

     string fileName = @"C:\Users\UserName\Desktop\Book1.xlsx";
                Application excelApp = new Application();
                excelApp.Visible = true;
                Workbook aWorkbook = excelApp.Workbooks.Open(fileName);
              
                Chart aChart = aWorkbook.Worksheets["Sheet1"].ChartObjects[1].Chart;
              
                aChart.FullSeriesCollection(1).Points[4].Select();
                Microsoft.Office.Interop.Excel.FillFormat fill = excelApp.Selection.Format.Fill;
                fill.Visible = MsoTriState.msoTrue;
                Color color = Color.FromArgb(0,255,255);
    
                fill.ForeColor.RGB = color.ToArgb();
                fill.Transparency = 0;
                fill.Solid();
     

    Also as John motioned, record macro is a good way for us learning Excel object model.

    Hope it is helpful.

    Regards & Fei



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 19, 2015 3:31 AM
    Moderator