Line graph colors

  • Thread starter Thread starter big_divot
  • Start date Start date
B

big_divot

my spreadsheet contains 3 columns, a date, tournament type, Amount. Each
tournament type is color coded, so it shows a general idea of money won or
lost in the tournaments. I set up a separate worksheet to create a line
graph. and entered a formula =c2 + b1 and copied the range to the bottom, so
it gives me a running total of the tournament winnings and losings. I change
the formulas to a value, using paste special, then I change the color in the
column, using the paste special to format the colors which I have used, so
the column of the running total also contains the colors.
When I graph using the 2d line graph I get a nice graph which looks like a
stock market graph, however its one color. I would like to represent the
colors on the graph, showing the downturns and upturns based on the
tournament type by using the colors in the selected cells. Can anyone offer
a sugestion? thanks.
 
Am I missing something. Doesn't the up and down of the line give you that
exact visual representation?
 
Hi,

Easiest with VBA code to set the point colour.
This example applys a colour based on positive/negative variation
between points.

'------------------------------------
Sub X()
Dim vntValues As Variant
Dim objPoint As Point
Dim lngItem As Long
Dim lngPoint As Long
With ActiveChart
With .SeriesCollection(1)
vntValues = .Values
lngPoint = 1
For lngItem = LBound(vntValues) + 1 To UBound(vntValues)
lngPoint = lngPoint + 1
If vntValues(lngItem) < vntValues(lngItem - 1) Then
' negative
.Points(lngPoint).Border.ColorIndex = 3 ' red
Else
' same or positive
.Points(lngPoint).Border.ColorIndex = 10 ' green
End If
Next
End With
End With

End Sub
'-----------------------------------

You can adapt this to colour according to cell interior colour by
checking cells rather than series point values.
If the cells are coloured using Conditional formatting then you will
need to do the same test in code to determine whether to apply the
colours or not. The chart will not automatically pick up colour
information from cells.

Cheers
Andy
 
Thanks for replying.

It does, however there are multiple tournament types, rebuy, single buy in
tournaments, and Turbo. I set up a bar chart which gives my boss the
results, for each tournament type, but when I copied the line chart into
paint and used the visual colors to show him the results, it gave a much
clearer representation of where he was winning and losing money. It looks
like the next post has a solution, but its been about 10 years or more since
I used any vb in a spreadsheet. Thanks again.
 
Thanks, I think this is the way to go, however its been 10 yrs since I used
any vb in excel. I recalled, that I would have to select the range of cells
that I was operating on, and I didnt see that in the script. I will give it
a try this evening when I get to work. Thanks.
 
Back
Top