Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from Sep 28 '12 at 16:00

This question came from our site for professional and enthusiast programmers.

up vote 1 down vote accepted

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
      '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.

share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .