• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hourly goals chart...

DaveM

New Member
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!
upload_2016-8-3_12-27-32.png
 

Hui

Excel Ninja
Staff member
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

New Member
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!
 

Attachments

vletm

Excel Ninja
Something like this ...
You can set even own color to every part of it if needed.
Screen Shot 2016-08-04 at 21.12.56.png
I tested with HBH Board.xlsx's Sheet1 -chart with next code:
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'.
 

Hui

Excel Ninja
Staff member
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
upload_2016-8-5_21-56-53.png

upload_2016-8-5_21-57-29.png

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

Attachments

Top