Fix Excel VBA Macro to Convert HTML Color Text into Cell Formatting
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.
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.
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.
In WPS Office Excel, open the .xlsm workbook and press Alt+F11 to open the VBA editor.
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
Close the VBA editor and select only the cells that contain the HTML font color tags.
Click Developer > Macros or press Alt+F8, select ChangeColor, and click Run.
Check that the selected cells now have the intended background colors and that the <FONT COLOR=...> markup has been removed from the cell text.

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. Open the workbook in WPS Office: Launch WPS Office Excel and open the .xlsm workbook that contains the exported data.
- 2. Open the VBA editor: Press Alt+F11 and click Insert > Module.
- 3. Paste the ChangeColor macro: Paste the ChangeColor VBA code into the module and close the editor.
- 4. Run the macro: Select the affected cells, press Alt+F8, choose ChangeColor, and click Run.

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.




