VBA Chart series set Color Fill to “Automatic”

问题:

I want to highlight some series in my stack chart - this is I have implemented. But the chart is dynamic, so when I change selection I need to highlight other series. But what was highlighted previously remains highlighted.

I am thinking to tun a cycle through all series and assign Fill Color of the series as "Automatic" each time when I change the source. Then I could highlight the needed series.

I have have found 2 options to sent the color using properties

.Interior.Color

• OR

.Format.Fill.ForeColor.SchemeColor

But there I used RGB color model to set a color.

How can I come back to "Automatic" color, that is default color in my chart template. What value should I assign to the properties above?

Thanks!

回答1:

I have used: .Interior.Color = xlNone

回答2:

Is it a custom chart template? If so, you may have a custom list of RGB values for the template.

Otherwise you may simply use the default colors of the workbook theme.

This code reapplies the six default workbook theme colors to the chart. If there are more than six series, the theme colors repeat. Excel also changes the brightness for subsequent sets of six series, but I have not bothered. If you need to adjust brightness, I can come back implement it.

Sub ReapplyDefaultColors()
  Dim iSrs As Long, nSrs As Long
  Dim iThemeColor As MsoThemeColorIndex
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart"
  Else
    iThemeColor = msoThemeColorAccent1
    With ActiveChart
      nSrs = .SeriesCollection.Count
      For iSrs = 1 To nSrs
        .SeriesCollection(iSrs).Format.Fill.ForeColor.ObjectThemeColor = _
            iThemeColor
        iThemeColor = iThemeColor + 1 ' msoThemeColorAccent2, 3, 4, etc.
        If iThemeColor > msoThemeColorAccent6 Then
          ' recycle colors
          ' should also adjust brightness
          iThemeColor = msoThemeColorAccent1
        End If
      Next
    End With
  End If
End Sub

Edit: It turns out that we can use the deprecated but still working syntax from Excel 2003 to reapply the automatic formatting to a chart series:

Sub ReapplyDefaultColors()
  Dim iSrs As Long, nSrs As Long
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart"
  Else
    With ActiveChart
      nSrs = .SeriesCollection.Count
      For iSrs = 1 To nSrs
        .SeriesCollection(iSrs).Interior.ColorIndex = xlAutomatic
      Next
    End With
  End If
End Sub

回答3:

I'm using EXCEL2010. To reset in automatic my bars colour I use this syntax:

istogramma.Interior.Pattern = xlAutomatic

Where istogramma is the name of my series