+ Reply to Thread
Results 1 to 5 of 5

VBA to change the bar fille color in chart

  1. #1
    Registered User
    Join Date
    05-18-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 2010
    Posts
    4

    VBA to change the bar fille color in chart

    Hi,

    I'm new to here, and new to excel VBA. Could anyone help me with my problem? Thank you so much!

    I'm setting up a VBA to change the bar fille color in chart.
    For the VBA, I pre-set the chart series up to 5. It's work if the chart contains 5 or more series, but if the chart only contains last than 5 series, error occurred. How should I do to fix this problem?


    Thank you so much for your time, and your help!

    Here is my VBA:

    ActiveChart.SeriesCollection(1).Select
    Selection.Format.Line.Visible = msoFalse
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Solid
    End With
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(246, 221, 219)
    .Transparency = 0
    .Solid
    End With
    ActiveChart.SeriesCollection(2).Select
    Selection.Format.Line.Visible = msoFalse
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(246, 221, 219)
    .Solid
    End With
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(238, 189, 188)
    .Transparency = 0
    .Solid
    End With
    ActiveChart.SeriesCollection(3).Select
    Selection.Format.Line.Visible = msoFalse
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(238, 189, 188)
    .Solid
    End With
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(228, 154, 152)
    .Transparency = 0
    .Solid
    End With
    ActiveChart.SeriesCollection(4).Select
    Selection.Format.Line.Visible = msoFalse
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(228, 154, 152)
    .Solid
    End With
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(220, 120, 118)
    .Transparency = 0
    .Solid
    End With
    ActiveChart.SeriesCollection(5).Select
    Selection.Format.Line.Visible = msoFalse
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(220, 120, 118)
    .Solid
    End With
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(208, 79, 76)
    .Transparency = 0
    .Solid
    End With
    End Sub

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: VBA to change the bar fille color in chart

    Possibly with using a For loop...
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-18-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: VBA to change the bar fille color in chart

    Thank you so much for your help dangelor, will try your method and see if it is working,

  4. #4
    Registered User
    Join Date
    05-18-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: VBA to change the bar fille color in chart

    I'm so sorry to bother again, now I can only change all series into same colour now, if I would like to make it difference colour, what I need to change in the VBA? Thank you so much again!


    For i = 1 To ActiveChart.SeriesCollection.Count
    ActiveChart.SeriesCollection(i).Select
    Selection.Format.Line.Visible = msoFalse
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(246, 221, 219)
    .Transparency = 0
    .Solid
    End With
    Next i
    End Sub

  5. #5
    Registered User
    Join Date
    05-18-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: VBA to change the bar fille color in chart

    No worries now. I finally found a way to fix it

    For i = 1 To ActiveChart.SeriesCollection.Count
    If i = 1 Then
    ActiveChart.SeriesCollection(1).Select
    Selection.Format.Line.Visible = msoFalse
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(246, 221, 219)
    .Transparency = 0
    .Solid
    End With
    ElseIf i = 2 Then
    ActiveChart.SeriesCollection(2).Select
    Selection.Format.Line.Visible = msoFalse
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(238, 189, 188)
    .Transparency = 0
    .Solid
    End With
    ElseIf i = 3 Then
    ActiveChart.SeriesCollection(3).Select
    Selection.Format.Line.Visible = msoFalse
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(228, 154, 152)
    .Transparency = 0
    .Solid
    End With
    ElseIf i = 4 Then
    ActiveChart.SeriesCollection(4).Select
    Selection.Format.Line.Visible = msoFalse
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(220, 120, 118)
    .Transparency = 0
    .Solid
    End With
    ElseIf i = 5 Then
    ActiveChart.SeriesCollection(5).Select
    Selection.Format.Line.Visible = msoFalse
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(208, 79, 76)
    .Transparency = 0
    .Solid
    End With
    End If
    Next i
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA code to change color of bar chart
    By nmckever in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-11-2014, 05:13 PM
  2. [SOLVED] Possible to change color of a bar chart based on it's value?
    By Consty1 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-13-2013, 02:17 PM
  3. change the color of a line in a chart
    By amartino44 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-30-2013, 12:36 PM
  4. Change chart color
    By Pindacko in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-05-2007, 03:36 PM
  5. [SOLVED] How to change color scheme in pie chart?
    By Campus Staff in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-23-2006, 02:55 PM
  6. help me to change the color of two series in a chart into the same
    By KhanhNguyen in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-20-2006, 11:48 PM
  7. [SOLVED] Bar chart: change color of a bar to red if value > x?
    By Christian Münscher in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-01-2005, 03:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1