Chart & Visualization Issues

How to Automate Excel Pie Chart Slice Colors with VBA

John WilsonJohn Wilson Nov 3, 2026 871 views

Question details

The user needs to assign and lock consistent colors to pie chart slices across multiple datasets using a VBA macro.

Automate Excel Pie Chart Slice Colors with VBA
Product
Excel
Device & OS
not provided
Scenario
Visualizing changing data across multiple datasets where default pie chart colors dynamically shift and need to be standardized.
Observed behavior
Pie chart slice colors automatically change when the underlying categories or submitted values update, causing inconsistency across reports.
Before you start

Ensure the Developer tab is enabled in your spreadsheet application and create a backup copy of your workbook before running any new VBA macros.

Solution 1Recommended

Create a VBA Macro to Assign Consistent Pie Chart Colors

Use a VBA loop to iterate through chart data points and apply specific RGB colors based on category names or point indexes.

When data categories change, spreadsheet software dynamically assigns default colors based on the new point index. By utilizing a VBA macro, you can map specific category names to fixed RGB values, ensuring that particular data points always retain the same color regardless of their position or visibility in the dataset.

1
Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

2
Insert a New Module

In the VBA editor, click 'Insert' from the top menu and select 'Module' to create a blank script window.

3
Write the Macro Code

Write a VBA procedure that loops through 'ActiveSheet.ChartObjects' and evaluates 'SeriesCollection(1).XValues'. Use a Select Case statement to set 'Format.Fill.ForeColor.RGB' based on the category name.

4
Run the Macro

Close the VBA editor, return to your worksheet, and run the macro via the Developer tab or by pressing Alt + F8. Apply this to a test chart first.

Create a VBA Macro to Assign Consistent Pie Chart Colors
Adapt and Test the Code: Always adapt the starting VBA code to your specific worksheet, chart names, data ranges, and preferred color mapping. Test it extensively on a copy of the workbook before applying it to multiple data sets.
Efficient Spreadsheet Data Visualization

Manage Charts and Macros Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful data visualization tools and robust macro support. You can easily manage chart styles, customize colors, and run VBA scripts to automate your reporting workflow without compatibility issues.

  1. 1. Open your Workbook: Launch WPS Spreadsheet and open your macro-enabled workbook containing the pie charts.
  2. 2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon to access the Macro and VBA editor.
  3. 3. Run your Script: Insert your VBA code to assign consistent RGB colors and execute the script directly within the WPS Spreadsheet environment.
  4. 4. Customize Chart Styles: Alternatively, double-click any pie chart to open the right-hand properties panel and manually set consistent fill colors without writing code.
Fully compatible with Microsoft Excel (.xlsx, .xls) and macro-enabled workbooks (.xlsm)Advanced developer tools for executing VBA scripts and automating chart formattingIntuitive chart editing panel to manually lock slice colors without codingLightweight application with fast processing for visualizing large datasets
microsoft office alternative - wps office

Frequently Asked Questions

Why do pie chart colors change when I filter my data?

By default, spreadsheet applications assign colors dynamically based on the index (order) of the visible data points. If a category is filtered out, the remaining categories shift their index positions and take on new default colors.

Can I lock pie chart colors without using VBA?

Yes. You can manually format each data point. Click on a specific pie slice twice (slowly) until only that slice is selected, then change its fill color from the format menu. This manual color assignment typically persists even if the data changes.

How do I find the correct RGB values for my VBA macro?

You can find RGB values by opening the fill color palette in your spreadsheet software, selecting 'More Colors,' and viewing the 'Custom' tab. This will display the exact Red, Green, and Blue numerical values.

Will macros written in Excel work in WPS Office?

Yes, WPS Office fully supports Excel VBA macros in its advanced versions. You can open .xlsm files and execute your existing chart formatting scripts natively.