News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Chart Expert Needed re Bar Chart Colors

    Posted on krweaver Comment on the AskWoody Lounge

    Home Forums AskWoody support Productivity software by function MS Excel and spreadsheet help Chart Expert Needed re Bar Chart Colors

    This topic contains 4 replies, has 3 voices, and was last updated by  WSrory 2 years, 7 months ago.

    • Author
      Posts
    • #508274 Reply

      krweaver
      AskWoody Lounger

      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?

    • #1593828 Reply

      Maudibe
      AskWoody_MVP

      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
      
      Attachments:
      • #1593837 Reply

        WSrory
        AskWoody Lounger

        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.

    • #1593847 Reply

      Maudibe
      AskWoody_MVP

      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?

    • #1593850 Reply

      WSrory
      AskWoody Lounger

      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
      
    • #1593876 Reply

      Maudibe
      AskWoody_MVP

      Sweet!

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Chart Expert Needed re Bar Chart Colors

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.