VBA & Macro Problems

How to Set Chart Colors Based on Category Names Using Excel VBA

Kushani Nimanthika Oct 8, 2026 869 views

Question details

The user needs an automated way to assign specific colors to chart data points depending on their category labels.

How to Set Chart Colors Based on Category Names Using Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Creating dynamic charts where the color of each data point must automatically reflect specific text-based categories, such as Canada, US, and International.
Observed behavior: Currently, the user is forced to select chart points manually to change their colors, which is inefficient and difficult to maintain.

Before you start

Ensure your workbook contains a chart with data series and X-axis category labels properly defined, and always save a backup copy before running new macros.

Solution 1Recommended

Use a VBA Macro to Assign Colors by Category Name

This method utilizes a VBA script to iterate through the data points of a selected chart, checking the X-axis category labels and applying specific RGB colors.

Because Excel does not offer a native formula solution for dynamic chart coloring based on text labels, VBA is the most efficient method. This script checks the XValues of your chart series.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications editor. Go to Insert > Module to create a new blank module.

Insert the Color-Coding Script

Write a script that loops through the Points collection of ActiveChart.SeriesCollection(1). Extract the XValues array to check the category name for each point.

Assign Colors using Select Case

Use a Select Case statement to define colors. For example, Case "Canada" applies RGB(255, 0, 0), Case "US" applies RGB(0, 0, 255), and Case "International" applies RGB(0, 255, 0).

Run the Macro

Return to your worksheet, click on the chart you want to format so it becomes the ActiveChart, then press Alt + F8, select your new macro, and click Run.

Use a VBA Macro to Assign Colors by Category Name

Chart Type Compatibility: This script works best for standard column, bar, or pie charts where category labels are clearly mapped to the primary horizontal axis.

Solution 2

Provide a Sanitized Workbook for Custom Support

If your chart structure is complex or the standard VBA script fails, sharing a sample workbook allows developers to tailor the code specifically to your chart type.

Efficient Excel VBA Alternative

Use WPS Spreadsheet to Run Your VBA Macros

WPS Spreadsheet offers excellent VBA and macro support, allowing you to automate chart formatting and data analysis just like you would in Microsoft Excel.

1. Open Your Macro Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the chart.
2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon and click on 'Visual Basic' or 'Macros' to open the built-in editor.
3. Execute the Code: Select your chart on the sheet, return to the Developer menu, click 'Macros', select your color-coding script, and hit 'Run'.

Seamlessly runs standard Excel VBA code and macro modulesFully compatible with Microsoft Excel formats including .xlsm and .xlsxLightweight program offering a familiar, easy-to-navigate interfaceCompletely free with built-in advanced charting tools

microsoft office alternative - wps office

Frequently Asked Questions

Can I use standard conditional formatting instead of VBA for charts?

No, standard Excel conditional formatting applies only to cells, not to chart data points. To dynamically color chart points based on text values, you must either use a VBA macro or set up complex helper columns for each category.

Why is my VBA macro returning a 'Subscript out of range' error?

This error typically occurs if you try to run the macro without selecting the chart first (meaning there is no ActiveChart), or if the code references a specific SeriesCollection index that does not exist in your chart.

Do I need to save my workbook in a specific format to keep the macro?

Yes, any workbook containing VBA scripts must be saved as an Excel Macro-Enabled Workbook (.xlsm). Saving it as a standard .xlsx file will strip the VBA code from the document entirely.

Excel & Data Problems