0

I'm using MS Excel 2010.

I have a worksheet that looks like this

---/ 1001 / 1002 / 1003 / 1101 / 1102 / 1201 / 1202 / 1203 / 1204
R2 / ... 
R3 / ...
...

I would like to create a line chart with each column a series but coloured so that all series with the same first two digits are the same colour but different shades (so 10** would all be red (say) but increasingly lighter shades of red).

Any suggestions? Thanks for any help.

1

You can use something like this:

Sub ColorLines()

  Dim objSeries As series

  Dim strLastDigits As String

  Dim lngColorIndex As Long

  lngColorIndex = 2
  strLastDigits = ""

  For Each objSeries In Diagramm1.SeriesCollection

    If Left(objSeries.Name, 2) <> strLastDigits Then
      'set new color
      lngColorIndex = lngColorIndex + 1
      objSeries.Border.ColorIndex = lngColorIndex
    Else
      'set shade of current color
      If objSeries.Border.Color > 50 Then
        objSeries.Border.Color = objSeries.Border.Color - 50
      End If
    End If

    strLastDigits = Left(objSeries.Name, 2)
  Next objSeries

End Sub

BUT - the concrete algorithm to automate the shading and coloring is up to you ;)

This is just an example, of how you could do this.

  • Sorry, but what does "Diagramm1." do? – Hugh Sep 28 '12 at 8:05
  • oh, jeah, this is my chart-object, you can replace it with some other chart-object - like ActiveSheet.ChartObjects(1) or whatever suits your needs. – Jook Sep 28 '12 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.