Line graph colors
 

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.

Line graph colors
 

Am I missing something. Doesn't the up and down of the line give you that
exact visual representation?

--
__________________________________
HTH

Bob

"big_divot" wrote in message
...
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.

Line graph colors
 

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

big_divot wrote:
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.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Line graph colors
 

You could try a waterfall chart:

http://peltiertech.com/Excel/Charts/Waterfall.html

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



big_divot wrote:
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.

Line graph colors
 

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.

"Bob Phillips" wrote:

Am I missing something. Doesn't the up and down of the line give you that
exact visual representation?

--
__________________________________
HTH

Bob

"big_divot" wrote in message
...
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.

Line graph colors
 

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.

"Andy Pope" wrote:

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

big_divot wrote:
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.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info