Excel帕累托图需求：依据累计百分比设置对应计数系列点颜色

Hey Sara, let's get that Pareto chart coloring working properly! It sounds like your core logic is solid—we just need to make sure we're correctly referencing the chart series and targeting the right data points. Here's a step-by-step solution:

Common Pitfalls to Check First #

Before diving into code, double-check these quick things that often cause silent failures:

• Confirm the index numbers for your series: Is your cumulative percentage series really SeriesCollection(pe...)? Open your chart, right-click the cumulative line, and check its order in the Select Data dialog—indices start at 1, not 0.
• Make sure your cumulative percentage values are stored as numbers, not text. If they're formatted as text, the <80% comparison won't work as expected.

Corrected VBA Code #

Here's a refined version of your code that should properly target the corresponding count series points when cumulative percentage is below 80%:

Sub FormatParetoChart()
    Dim paretoChart As ChartObject
    Dim countSeries As Series
    Dim percSeries As Series
    Dim i As Integer
    Dim targetColor As Long
    
    ' Set your target chart (adjust the sheet/chart name as needed)
    Set paretoChart = ThisWorkbook.Sheets("YourSheetName").ChartObjects("ParetoChart")
    
    ' Define your two series (confirm indices match your chart!)
    ' Example: countSeries is the bar chart, percSeries is the cumulative line
    Set countSeries = paretoChart.Chart.SeriesCollection(1)
    Set percSeries = paretoChart.Chart.SeriesCollection(2)
    
    ' Set your desired color (e.g., RGB(255, 165, 0) for orange)
    targetColor = RGB(255, 165, 0)
    
    ' Reset all count series points to default color first (optional but clean)
    For i = 1 To countSeries.Points.Count
        countSeries.Points(i).Fill.ForeColor.RGB = RGB(192, 192, 192) ' Default gray
    Next i
    
    ' Traverse each cumulative percentage point
    For i = 1 To percSeries.Points.Count
        ' Get the actual value of the cumulative percentage (not displayed text)
        Dim percValue As Double
        percValue = percSeries.Values(i)
        
        ' Check if value is less than 80% (0.8 in decimal)
        If percValue < 0.8 Then
            ' Set corresponding count series point to target color
            countSeries.Points(i).Fill.ForeColor.RGB = targetColor
        End If
    Next i
    
    ' Force chart refresh to ensure changes show up
    paretoChart.Chart.Refresh
End Sub

Key Fixes & Explanations #

• Explicit Series References: We explicitly define both the count and percentage series to avoid confusion with indices.
• Decimal Comparison: We use 0.8 instead of 80% to ensure numerical accuracy (VBA handles percentages as decimals internally).
• Value Extraction: Using percSeries.Values(i) gets the raw numerical value, not the formatted text that might appear in the chart.
• Chart Refresh: Adding .Refresh ensures any pending updates are rendered immediately, which fixes cases where changes don't show up right away.

Troubleshooting Tips #

If it's still not working:

1. Add a Debug.Print percValue inside the loop to verify the values being read are correct.
2. Check if your count series is a clustered column type—some chart types don't support per-point coloring the same way.
3. Confirm that i is correctly aligning between the two series (both should have the same number of data points).

Let me know if you need to tweak this for your specific chart setup!

内容的提问来源于stack exchange，提问作者Sara Long