How to Automate Excel Pie Chart Slice Colors with VBA
Question details
The user needs to assign and lock consistent colors to pie chart slices across multiple datasets using a VBA macro.

- 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.
Ensure the Developer tab is enabled in your spreadsheet application and create a backup copy of your workbook before running any new VBA macros.
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.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the VBA editor, click 'Insert' from the top menu and select 'Module' to create a blank script window.
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.
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.

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. Open your Workbook: Launch WPS Spreadsheet and open your macro-enabled workbook containing the pie charts.
- 2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon to access the Macro and VBA editor.
- 3. Run your Script: Insert your VBA code to assign consistent RGB colors and execute the script directly within the WPS Spreadsheet environment.
- 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.

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.




