VBA & Macro Problems

How to Fix Excel VBA Macro for Coloring Unique Text Values

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs an Excel VBA macro to assign specific colors to unique text values, ensuring that repeated values reuse their previously assigned color.

Product: Excel
Device & OS: not provided
Scenario: Running an automated VBA script to highlight distinct text entries within a user-selected range on a spreadsheet.
Observed behavior: The macro either fails entirely on the selected range or produces a subscript-out-of-range error because the color assignment or match lookup range is incorrectly configured.

Before you start

Before modifying your script, open the VBA Editor and locate the exact line where the cell's interior color is being assigned so you can replace the correct property.

Solution 1Recommended

Update VBA Code to Use ColorIndex and Dynamic Match Range

Fix the macro by replacing Interior.Color with Interior.ColorIndex and adjusting the Match function to search only previously processed cells above the current one.

The 'subscript-out-of-range' error typically occurs because `Interior.Color` returns a large RGB value, which exceeds the valid bounds if your code uses it as an index for an array. Switching to `Interior.ColorIndex` resolves this by utilizing standard integer values (1 to 56).

Additionally, when processing a specific selected range rather than an entire column, the `Match` function must dynamically evaluate only the cells above the current active cell to accurately identify if a value has already been assigned a color.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications editor, and locate the module containing your specific macro.

Replace Color with ColorIndex

Find the line of code that sets the cell color and change `Interior.Color` to `Interior.ColorIndex`. Ensure the assigned value is an integer between 1 and 56.

Adjust the Match Lookup Range

Modify the `Match` range parameter in your loop so it searches dynamically. For example, if 'i' is your current row variable, set the range to look from row 1 down to row 'i - 1' so it only checks above the current cell.

Define a Predefined Color List

Create an array in your code containing specific ColorIndex numbers (e.g., Array(3, 4, 5, 6)) to restrict the assigned colors to a predefined palette of your choice.

Test on a Selected Range

Close the editor, highlight a specific range of cells in your worksheet containing text, and execute the macro to confirm that unique values receive distinct colors and duplicates share the exact same color.

ColorIndex Limitations: Excel's ColorIndex property only supports integer values from 1 to 56. If your selected range contains more than 56 unique text values, your macro will need additional logic to either recycle colors from the beginning of the list or switch back to standard RGB values.

Advanced Spreadsheet Features

Run and Edit VBA Macros Seamlessly with WPS Office

WPS Spreadsheet provides excellent built-in support for Developer tools, allowing you to easily write, debug, and execute VBA macros such as unique text highlighting without complex workarounds.

1. Open your macro-enabled file: Launch WPS Spreadsheet and open the .xlsm file containing your unique-text coloring macro.
2. Access Developer Tools: Navigate to the 'Developer' tab on the main top ribbon.
3. Open the VBA Editor: Click on 'Visual Basic' to view your existing scripts, or click 'Macros' to run them.
4. Apply ColorIndex fixes: Edit your code to implement the ColorIndex and Match range fixes directly within the WPS VBA environment, then click 'Run'.

Fully compatible with Microsoft Excel .xlsm and .xlsb macro-enabled formatsBuilt-in VBA editor for seamlessly writing and debugging your codeEasily execute automated tasks like coloring unique text valuesLightweight architecture that processes large datasets quickly

microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between Interior.Color and Interior.ColorIndex in VBA?

Interior.Color requires a specific RGB value (like RGB(255, 0, 0) for red), which results in a very large number. Interior.ColorIndex, on the other hand, references a predefined palette of 56 standard colors using simple integers from 1 to 56.

How do I find the basic color indexes available in Excel VBA?

You can reference the standard 56-color palette. For example, 1 is Black, 2 is White, 3 is Red, 4 is Green, and 5 is Blue. To see them all, you can write a short VBA loop that sets `Cells(i, 1).Interior.ColorIndex = i` for values 1 through 56.

Why do I get a subscript out of range error when running my macro?

This error happens when your code tries to access an item in an array or collection that does not exist. In coloring macros, this often occurs if you accidentally pass a massive RGB value into a function or array that is only designed to accept a small ColorIndex integer.

How do I limit a VBA Match function to only search cells above the current selection?

Within your VBA loop, define the search range dynamically using your loop counter variable. For instance, if 'i' represents your current row, instruct the Match lookup range to evaluate exclusively from the starting cell down to row 'i-1'.

Excel & Data Problems