Formatting Chart Data

[LeeStallan]

Hi all,
I know this question has been asked over and over again, but no matter how much I search I dont seem to able to find a suitable resolution.

My challenge would seem to be simple, but I am unable to sort it out by myself.
I work in a call centre and I record my daily figures. My target is 82%, and I would like a bar chart in Excel 2003 to colour the chart according to wether I hit target or not. If my daily figure >=82% the bar should be green. If daily figure <82% bar should be red.

I'm looking to use VBA to achieve this as I have a lot of data recorded on spreadsheet already, and dont want to use the 'multiple range extended table'. Ideally I would like to add the code to the Autpen macro which I have.

Any help or pointers will be GREATLY appreciated

Thanks
Lee

 

[Son]

ok, so try this:

Sub Test2()




Dim Day, DataRow, ChartNumber

'---------------------------------------------------------------
For Day = 1 To 31   'this is for january. IT CHANGES FOR EACH MONTH

DataRow = 1          ' this is the row that your data starts. IT CHANGES FOR EACH MONTH
ChartNumber = 1     'this is your first chart, ie january. IT CHANGES FOR EACH MONTH


     If Worksheets("Sheet1").Range("A" & DataRow) >= 82 Then
        Worksheets("Sheet1").ChartObjects("Chart" & ChartNumber).Activate
        ActiveChart.SeriesCollection(1).Select
        ActiveChart.SeriesCollection(1).Points(Day).Select
            
        With Selection.Interior
            .ColorIndex = 4      '(3 = red, 4 = green)
        End With
     Else
        Worksheets("Sheet1").ChartObjects("Chart" & ChartNumber).Activate
        ActiveChart.SeriesCollection(1).Select
        ActiveChart.SeriesCollection(1).Points(Day).Select
            
        With Selection.Interior
            .ColorIndex = 3     '(3 = red, 4 = green)
        End With
    
     End If
 
DataRow = DataRow + 1


Next
'--------------------------------------------------------------------------


 
End Sub

You need to copy the code for each month, each month changing the 3 numbers as needed.

The code is rather long and a bit unsorted, and you need to copy it 11 times, but I can't think of anything else, since you have all inputs for all months one after the other. Hope this helps.

 

[Jon Peltier]

If you do it as you're setting up the chart, you don't need to use VBA:
Conditional Formatting of Excel Charts

If the charts are already present (which sounds like your case) you can use something like this:
VBA Conditional Formatting of Charts by Value
It works on the active chart, and on all points in the chart, so number of days can vary.

 

[LeeStallan]

Jon,
I've already seen your post when I first searched for an answer to my problem in Google. I am trying to avoid having to add even more columns and formulas to a spreadsheet which already contains a lot of data.
I was hoping for a few lines of VBA which I can add to my Autpen macro so that the bar charts are updated every day when I start my shift (this spreadsheet is the first file I open)

 

[Son]

Actually the chart will be refreshed at the time that you input your daily percentage, as each bar will be linked to that particular cell, so it does not need to be via the on open procedure.

 

[Son]

Actually the chart will be refreshed at the time that you input your daily percentage, as each bar will be linked to that particular cell, so it does not need to be via the on open procedure.

 

[LeeStallan]

As much as anything, it is a convenient place to save the vba, without having to add a new Macro