Recently at work I was asked to create a bar chart that showed various divisions grouped by departmental assigned colors. For example, there are four departments assigned - Internal Medicine, Surgery, Medicare Specialties, and Other. Within each of these departments are various divisions. The task was to show each division as a bar and color that bar according to what department they were assigned to.

This can be done fairly easily (and without code) using an individual column assigned to a department. The rows consist of divisions and the data is assigned to the cell where the division and department intersect. The problem with this approach is that the chart source consists of as many series as there are departments. It is also difficult to maintain the correct colored bars if the data needs to be sorted.

In an effort to keep things simple my thought was to use just one series of data and use a macro to color the bars associated with that series. As an example, the following screenshot shows a worksheet with three columns - department, division, and revenue.

The macro below loops through the department column and assigns a color to the bars based on that department.

Sub ColorBars()

    Application.ScreenUpdating = False

    Dim Rng As Range
    Dim Cnt As Integer

    Cnt = 1

    For Each Rng In Range("B3:B10")
        Set Pts = ActiveChart.SeriesCollection(1).Points(Cnt)
        If Rng.Value = "IM" Then
            Pts.Interior.ColorIndex = 24
        ElseIf Rng.Value = "SURG" Then
            Pts.Interior.ColorIndex = 15
        ElseIf Rng.Value = "MS" Then
            Pts.Interior.ColorIndex = 19
        ElseIf Rng.Value = "OTHER" Then
            Pts.Interior.ColorIndex = 35
        End If
        Cnt = Cnt + 1
    Next Rng
End Sub

The final product looks like the chart below.