VBA & Macro Problems

Fix Excel VBA Macro to Convert HTML Color Text into Cell Formatting

Chanuka GeekiyanageChanuka Geekiyanage Nov 13, 2026 868 views

Question details

The user needs a working VBA macro that scans selected cells, converts HTML-style font color tags into cell background colors, and removes the formatting markup from the cell text without crashing the application.

Product
WPS Office Excel
Device & OS
not provided
Scenario
A user exported data that includes HTML font color tags inside cells and wants to turn those tags into actual cell background formatting while keeping the cell values clean.
Observed behavior
The existing VBA macro code causes a major crash when run, so the color tags are not converted and the application may close unexpectedly.
Before you start

Work on a copy of the exported workbook and make sure the file is saved as a macro-enabled .xlsm workbook. If your data is in a CSV file, save or open a copy as .xlsm before adding the macro.

Solution 1Recommended

Run the ChangeColor Macro from a Standard VBA Module

Use this solution when the macro is pasted into the wrong module or run from a CSV file, which can crash the application or prevent the code from running.

CSV files cannot store or run VBA macros. The macro must be saved in an .xlsm workbook and placed in a standard module, not in a worksheet or workbook code module.

The code checks the selected cells for a leading <FONT COLOR= tag, reads the next color value, applies it to the cell interior, and then removes the entire opening tag from the cell text.

1
Open the VBA editor

In WPS Office Excel, open the .xlsm workbook and press Alt+F11 to open the VBA editor.

2
Insert a standard module

Click Insert > Module. Paste the following code into the module: Sub ChangeColor() Dim cell As Range For Each cell In Selection If UCase(Left(cell.Value, 12)) = "<FONT COLOR=" Then cell.Interior.Color = "&H" & Mid(cell.Value, 15, 8) cell.Value = Mid(cell.Value, 24, Len(cell.Value)) End If Next cell End Sub

3
Select the affected cells

Close the VBA editor and select only the cells that contain the HTML font color tags.

4
Run the macro

Click Developer > Macros or press Alt+F8, select ChangeColor, and click Run.

5
Verify the result

Check that the selected cells now have the intended background colors and that the <FONT COLOR=...> markup has been removed from the cell text.

Run the ChangeColor Macro from a Standard VBA Module
Macro runs without crashing: Placing the code in a standard module and running it on a saved .xlsm workbook prevents the crash and applies the color formatting correctly.
Recommended WPS Office Excel fix

Convert HTML Color Text to Cell Formatting with a VBA Macro in WPS Office Excel

Run the corrected VBA macro in WPS Office Excel to read HTML font color tags, apply the matching background color to each selected cell, and remove the markup from the cell text. The same .xlsm workbook and VBA code work in WPS Office without converting the file format.

  1. 1. Open the workbook in WPS Office: Launch WPS Office Excel and open the .xlsm workbook that contains the exported data.
  2. 2. Open the VBA editor: Press Alt+F11 and click Insert > Module.
  3. 3. Paste the ChangeColor macro: Paste the ChangeColor VBA code into the module and close the editor.
  4. 4. Run the macro: Select the affected cells, press Alt+F8, choose ChangeColor, and click Run.
Correctly converts HTML font color tags into cell background colorsRemoves HTML markup from cell text after applying formattingSupports .xlsm and .xlsx Microsoft Excel workbook formatsFree, lightweight Office suite with a familiar ribbon and VBA editor
microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro disappear when I save the file as CSV?

CSV files store only plain cell values and cannot keep VBA code. Save the workbook as a macro-enabled .xlsm file in WPS Office Excel to preserve the macro.

Where should I paste the ChangeColor code so it does not crash?

Open the VBA editor with Alt+F11, click Insert > Module, and paste the code into that standard module. Avoid placing the code in a worksheet or ThisWorkbook module.

How does the macro convert an HTML font color tag into a cell color?

The macro checks each selected cell for a leading <FONT COLOR= tag, reads the color value inside the tag, applies that value to cell.Interior.Color, and then removes the opening tag from the cell text.

Can I run this macro in WPS Office Excel?

Yes. Open the macro-enabled .xlsm workbook in WPS Office Excel, press Alt+F11 to open the VBA editor, and run the macro from the Developer tab or by pressing Alt+F8.

What should I do before running the macro on my original data?

Test the macro on a copy of the workbook first. Select only the cells that contain HTML font color tags, then run ChangeColor and verify that the colors are correct and the markup is removed.