Hourly goals chart...

[DaveM]

Hello... I'm not even sure this can be done in Excel, but here goes! I am trying to figure out a way to make a chart similar to the image attached to this post. It doesn't have to be a donut, but that would be preferable. I am trying to format the color each cell of the series based on a given value (Pass, Warning, Fail, or 0,1,2,3...). This is easy to do in a worksheet with conditional formatting, but trying to get each block or gradient of the data series to format has me stumped! I have some VBA code to set the color of a given series, but as soon as I add additional data, the new data reverts back to the colors in the chart style.

Thank you!

 

[Hui]

Can you post a sample file please

Is the chart dynamic in that it updates throughout the day or via a variable controlling the time?

 

[DaveM]

Thanks for replying, Hui! My goal is to have this updated dynamically with data that is pushed to the Excel file from Access. The Excel file EPIC SQDC.xlsm has the data I was originally working with to try to create the chart. Sheet1 has a grid of cells laid out with conditional formatting that works well, based on the parameters I set up. The HBH Board.xlsx file is one that one of my team members created just to visualize the concept, but I think it might be able to be used for the final solution. It is displaying a donut chart with all of the labels updated from the data in the cells. I just found out about this Excel chart late yesterday and haven't had time to pick it apart and see what's going on. If this requires VBA programming, that is no problem. I am much more familiar with the Access object model, but I can fumble my way thru the Excel object model!

 

[Hui]

How would this go as a start?


see attached file:

 

[vletm]

Something like this ...
You can set even own color to every part of it if needed.

I tested with HBH Board.xlsx's Sheet1 -chart with next code:

Sub Set_Colors()
    With Sheets("Sheet1").ChartObjects(1).Chart
        For c = 1 To 4
            For p = 1 To 12
                .SeriesCollection(c).Points(p).Interior.ColorIndex = 12 * (c - 1) + p
            Next p
        Next c
    End With
End Sub

Just modify which color do You want to use in which 'Point'.

 

[DaveM]

Wow! You guys ROCK! I will give these a look tomorrow when I get to work! Thank you!

 

[Hui]

I have implemented modified version of Vletm's code in a Worksheet change event in the worksheet of your file
As you enter values in the table teh chart will update as required




Please see attached file
The bottom table is required for the chart structure so please don't remove it