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]

Perhaps try sth like this:

 Sub Test1()

    Worksheets("Sheet1").ChartObjects("Graph 1").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Points(6).Select    'this is your bar, the 6th one


If Worksheets("Sheet1").Range("A3") >= 82 Then       'this is your cell with your %


    With Selection.Interior
        .ColorIndex = 4 '(3 = red, 4 = green)
    End With
   
Else


    With Selection.Interior
        .ColorIndex = 3    '(3 = red, 4 = green)
    End With
    
End If


    Range("B1").Select
End Sub

So, in Sheet1 you have the graph, your percentage is in range A1 and the bar you need to change is the 6th bar in the chart. Hope this helps.

 

[LeeStallan]

.

 

[LeeStallan]

I tried this, however it gets stuck on the line;

If Worksheets("2014").Range("I4:I146") >= 82 Then 'this is your cell with your %

The worksheet is called "2014". Cells I4 to I146 contain the data range that I want to format in the chart. This range of cells records my performance figure for each day I'm in work.

Not too sure what I need to change to get this to work :/

 

[Son]

how many charts do you have? one or one for each day?

the problem is with the range you specified: Range("I4:I146")

where is the result situated for the specific bar you are looking to change its colour? Each bar is associated with a single cell

 

[LeeStallan]

I have one chart per month, which records the values of each day in that month (approx 22 distinct pieces of data per chart).
Most days I exceed my target of 82% (green coloured bar), few days I dont meet 82% (red coloured bar)

 

[Son]

so, you have 22 bars in each graph?

 

[LeeStallan]

approximately. it depends how many days I work in the month (some months are 4 weeks, others are 5 weeks)

 

[Son]

you have to be more specific.

do you have a fixed range for each chart? All charts are in the same sheet? or do you have a sheet for each month? Similarly, is all the data recorded in one column in one sheet or in multiple sheets? if i don't know your ranges and the source of your chart, i cannot help

 

[LeeStallan]

I have one sheet for the entire year. All data is in one column, but sub-divided by month with a bar chart for each month. There is no fixed range as some months are a 4 week period others are 5 weeks