Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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