Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,536
    Thanks
    38
    Thanked 68 Times in 64 Posts

    Chart Expert Needed re Bar Chart Colors

    Wondering if there's a VBA way to change a bar chart's colors based on a range of cells.
    Suppose column A contained names of sales people and either the background in those cells or the background (or color #) in parallel column B indicated the bar chart colors, respectively. Could a bar chart be altered much the same way one would manually change the bar colors?

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,789
    Thanks
    139
    Thanked 705 Times in 639 Posts
    Here is some code where the user right clicks on a cell with the slaes person's name and runs a macro from the right context menu called "Change colors". The color palate shows and the user selects a color. The dialogue box closes, the cell changes to that color, and so does the colors of the columns in the bar graph.

    Note that in the bar graph you are describing, all of the names are in the same series therefore all the columns in the graph change to the same color. If you wish for each sales person to have a different color then the graph needs to be set up where each sales person is a series.

    Colors2.png

    Colors3.png

    Colors4.png

    ThisWorkbook Module
    Code:
    Private Sub Workbook_Deactivate()
        On Error Resume Next
                With Application
                    .CommandBars("Cell").Controls("ChangeColors").Delete
                End With
        On Error GoTo 0
    End Sub
    
    Private Sub Workbook_Open()
    Dim ChColor As CommandBarButton
        On Error Resume Next
            With Application
                .CommandBars("Cell").Controls("ChangeColors").Delete
                Set ChColor = .CommandBars("Cell").Controls.Add(Temporary:=True)
            End With
            With ChColor
               .Caption = "Change Colors"
               .Style = msoButtonCaption
               .OnAction = "ChangeColors"
            End With
        On Error GoTo 0
    End Sub
    In a standard module:
    Code:
    Sub ChangeColors()
    Set Rng = ActiveCell
    Application.Dialogs(xlDialogPatterns).Show
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    With Selection.Format.Fill
        .ForeColor.RGB = ActiveCell.Interior.Color
    End With
    Rng.Select
    End Sub
    Attached Files Attached Files

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,313
    Thanks
    3
    Thanked 211 Times in 194 Posts
    Quote Originally Posted by Maudibe View Post
    If you wish for each sales person to have a different color then the graph needs to be set up where each sales person is a series.
    You can have different colours for individual points in a bar chart, so you don't have to use separate series.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,789
    Thanks
    139
    Thanked 705 Times in 639 Posts
    You can have different colours for individual points in a bar chart, so you don't have to use separate series.
    I know you could format the markers on a line graph but I wasn't aware you could do this on a columnar bar graph. How is it done?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,313
    Thanks
    3
    Thanked 211 Times in 194 Posts
    Here's a simple example, based on your workbook layout that colours each bar to the same as each cell's fill colour:

    Code:
        Dim dataRange As Range
        Dim n As Long
        Set dataRange = Range("A2:A5")
        
        For n = 1 To dataRange.Cells.Count
            ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(n).Format.Fill.ForeColor.RGB = _
                                                    dataRange.Cells(n).Interior.Color
        Next n
    Regards,
    Rory

    Microsoft MVP - Excel

  6. The Following 2 Users Say Thank You to rory For This Useful Post:

    kweaver (2017-03-20),Maudibe (2017-03-20)

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,789
    Thanks
    139
    Thanked 705 Times in 639 Posts
    Sweet!

Tags for this Thread

Posting Permissions

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