VBA & Macro Problems

How to Keep Custom Colors in Excel Dynamic Charts Using VBA

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to prevent custom chart colors from reverting to default settings when reopening an Excel dashboard or changing dynamically selected data.

Product: Excel
Device & OS: not provided
Scenario: Updating or filtering data in a dynamic chart dashboard, usually controlled by drop-downs or dynamic formulas.
Observed behavior: Custom applied chart colors automatically reset to the default theme colors upon reopening the file or when the dynamic data selection refreshes.

Before you start

Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm), as you will need to use VBA to force the chart to retain your custom color formatting.

Solution 1Recommended

Use a VBA Macro to Reapply Chart Colors Dynamically

Since Excel does not natively save custom color mappings for changing dynamic chart data, a VBA script triggered by a drop-down change or recalculation will automatically reapply your chosen colors.

When dealing with dynamic charts linked to formulas or drop-down menus, Excel often overrides custom colors with default theme colors as the underlying series data changes or refreshes. Writing a simple VBA event macro is the most reliable way to enforce your custom color palette.

Open the VBA Editor

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

Locate the Target Worksheet

In the Project Explorer pane on the left side, double-click the specific worksheet containing your dynamic chart and drop-down menu.

Add the Worksheet Change Code

Select 'Worksheet' from the first drop-down at the top of the code window, and 'Change' from the second. Write a macro that targets your ChartObject (e.g., ActiveSheet.ChartObjects("Chart 1")) and assigns your specific RGB values to the SeriesCollection.Format.Fill.ForeColor.RGB whenever the target cell containing the drop-down is modified.

Save as Macro-Enabled

Close the VBA editor and save your file as an Excel Macro-Enabled Workbook (.xlsm) to ensure the code runs successfully every time the file is opened.

Identify Your Chart Name: Make sure to reference the exact name of your chart (e.g., 'Chart 1') in your VBA script. You can find the chart name in the Name Box next to the formula bar when the chart is selected.

Advanced Spreadsheets Tool

Create and Manage Dynamic Charts Seamlessly with WPS Office

WPS Spreadsheets provides powerful charting tools and full support for VBA macros, allowing you to easily maintain custom chart colors and design interactive dashboards without formatting issues.

1. Open Your Workbook in WPS: Launch WPS Spreadsheets and open your existing .xlsx or .xlsm file containing the dynamic chart.
2. Enable the Developer Tab: Go to the 'Developer' tab on the top ribbon and click 'Visual Basic' to access the macro editor.
3. Apply Chart Formatting Macros: Use the built-in VBA editor to insert your color-preservation code, ensuring your charts look perfect even when data refreshes.

Fully compatible with Microsoft Excel .xlsx and .xlsm formatsBuilt-in VBA/Macro support for automating complex chart formattingLightweight application that handles complex dynamic dashboards smoothlyFree to download and use with a highly intuitive, tabbed user interface

microsoft office alternative - wps office

Frequently Asked Questions

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

When data series change dynamically or empty series are recreated through formulas or filters, Excel redraws the chart using the active document theme colors, discarding any manual color overrides you previously applied.

Can I save my chart as a template to keep the custom colors?

Saving a chart template (.crtx) helps apply formatting to new charts manually, but it will not automatically enforce custom colors on an existing dynamic chart when its source data selection changes.

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

Select a shape or cell with your desired color, click the 'Fill Color' drop-down on the Home tab, select 'More Colors', and check the Custom tab to see the Red, Green, and Blue (RGB) values required for your code.

Excel & Data Problems