Chart Expert Needed re Bar Chart Colors

Topic

New Reply

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?

Reply | Quote

Replies

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.

46982-Colors2

46983-Colors3

46984-Colors4

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

Sweet!

Reply | Quote