Changing .Interior.ColorIndex has no effect

Question

In Excel 2003, when I change Series.Interior.ColorIndex to a value I need, it has no effect. It has an effect only when I first manually change color and then run the macro. Apparently this triggers some update mechanism. Does anyone have an explanation for this? Is there a way to somehow trigger this in the chart?.. I.e. make sure that the color changes take effect.

In addition, when I step through the code and watch ColorIndex, it does not change even after value is assigned. Is this one of the many bugs in Excel?

The code looks like this:

Sub DoStuff()
    Dim j As Long
    For j = 1 To ActiveChart.SeriesCollection.Count
        With ActiveChart.SeriesCollection(j)
           Select Case ActiveChart.SeriesCollection(j).Name
               Case "Milk"
                  .Interior.ColorIndex = 4
               Case "Cookies"
                  .Interior.ColorIndex = 28
               Case "Honey"
                  .Interior.ColorIndex = 26
            End Select
        End With
    Next j
End Sub

Edit: I am working with bar chart.

Answer 1

Try setting the border as well.

.Interior.ColorIndex = 4
.Border.ColorIndex = 4
.Border.Weight = xlMedium

Answer 2

Edit: In response to comments & edits to the original question:

I laid out an example Excel file, and I was able to get your code to work. Here is how my data is laid out:

   |    A    |  B  |  C  |  D  |
--------------------------------
 1 | Milk    |  3  |  1  |  4  |
 2 | Cookies |  1  |  5  |  9  |
 3 | Honey   |  2  |  6  |  5  |

And the bar graph looks something like this:

                          ._.
                          |C|
               ._.._.     |C|._.
 ._.   ._.     |C||H|  ._.|C||H|
 |M|._.|H|  ._.|C||H|  |M||C||H|
 |M||C||H|  |M||C||H|  |M||C||H|
---------------------------------
     1          2          3

Where all of the bars labeled "M" in the above diagram belong to the "Milk" series, all of the bars labeled "C" belong to the "Cookies" series, and all of the bars labeled "H" belong to the "Honey" series.

When I run your code on this chart, the bar colors are changed as expected. Can you tell me what is different between my setup and yours? I'll try to figure it out, but I need more information :)

Answer 3

probably a bug in Excel, which version of Excel are you using? make sure you service pack/patch it and try again to reproduce the problem.

Answer 4

i had the same problem, using excel 2007 and 2003.

i fixed it by opening the workbook in 2003, then formatting the data series by right-clicking (etc) and setting it's fill color to Automatic. then when i ran my macro, the colorindex setting part would take hold.