grayscale plot with bars

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chris.ammann@googlemail.com - 16 Jun 2006 14:48 GMT

Hi,

is Excel able to color the bars of a bar chart according to the height
of the respective bar?

The surface plot would be an alternative but I cannot change the
colors?

Can anybody help?
Chris

Reply to this Message

Andy Pope - 16 Jun 2006 15:18 GMT

Hi,

You might be able to use the conditional chart work around,
http://peltiertech.com/Excel/Charts/ConditionalChart1.html
http://www.edferrero.com/charting.aspx

On a surface chart you can adjust the value banding by double clicking
the legend.
If you select an individual legend entry you can then format it's colour.

Cheers
Andy

> Hi,
>
[quoted text clipped - 6 lines]
> Can anybody help?
> Chris

Signature

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

Reply to this Message

chris.ammann@googlemail.com - 16 Jun 2006 17:59 GMT

Thanks for your reply, Andy.

The method you suggest goes into the right direction but they take too
much handwork for what I had in mind.

As far as I understand it Excel just doesn't have the necessary options
integrated. I think I have to write a small VB piece.

Regards,
Chris

Reply to this Message

Andy Pope - 16 Jun 2006 19:47 GMT

Maybe this code, from one of my previous posts, will help.

'--------------------------------
Sub ColorColumns()

Dim vntValues As Variant
Dim intSeries As Integer
Dim intPoint As Integer
Dim objChart As ChartObject

For Each objChart In ActiveSheet.ChartObjects
With objChart.Chart
For intSeries = 1 To .SeriesCollection.Count
With .SeriesCollection(intSeries)
vntValues = .Values
For intPoint = 1 To .Points.Count
If vntValues(intPoint) < 60 Then
.Points(intPoint).Interior.Color = vbRed
ElseIf vntValues(intPoint) >= 60 And _
vntValues(intPoint) < 80 Then
.Points(intPoint).Interior.Color = vbYellow
Else
.Points(intPoint).Interior.Color = vbGreen
End If
Next
End With
Next
End With
Next

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

Cheers
Andy

> Thanks for your reply, Andy.
>
[quoted text clipped - 6 lines]
> Regards,
> Chris

Signature

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

Reply to this Message

renderman - 19 Jun 2006 11:37 GMT

Hi Andy,

my code looks more or less the same.

However, I tried to assign the colors via the RGB function but I ran
into another problem. I've the feeling that the color from the palette
that is closest to my RGB color is assigned. Is it possible to assign a
really arbitrary color?

Regards,
Chris

Reply to this Message

Andy Pope - 19 Jun 2006 12:42 GMT

Hi,

Not directly. As you say the colour nearest to one contained in the
palette is used. So you would need to adjust a colour within the palette
in order to use it in the chart.

Cheers
Andy

> Hi Andy,
>
[quoted text clipped - 7 lines]
> Regards,
> Chris

Signature

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

Reply to this Message

renderman - 19 Jun 2006 14:08 GMT

I feared that you would say that. A change in the palette will effect
all other charts in the workbook as well, correct?

Andy Pope schrieb:

> Hi,
>
[quoted text clipped - 16 lines]
> > Regards,
> > Chris

Reply to this Message