How to Change the Last Character Color in Excel Shape Text with VBA in WPS Office
Question details
The user needs a VBA macro that changes the font color of only the final check mark character in an Excel shape or button, without recoloring the preceding text.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Formatting a shape or button caption that ends with a check mark in an Excel-compatible workbook.
- Observed behavior
- The earlier Characters Start position of Len(buttonText)-1 selects the character before the final check mark, so the wrong character is recolored.
Open the target workbook in WPS Spreadsheets and press Alt+F11 to open the VBA editor. Confirm that the shape or button has the check mark as the final character before running the macro.
Use Characters(Start:=Len(buttonText), Length:=1) to color only the final check mark
Use this corrected VBA code when the check mark is the final character in a shape or button, so only that character changes color.
The Characters method is 1-based. If buttonText contains the full text, Len(buttonText) is the position of the final character. Setting Length:=1 limits the format change to that character, while Len(buttonText)-1 targets the character before it.
In WPS Spreadsheets, press Alt+F11 to open the VBA editor.
Click Insert and then Module to create a new module in the VBA editor.
Paste a sub that references the shape by name and sets the final character color, for example ActiveSheet.Shapes.Range(Array("CheckButton")).TextFrame.Characters(Start:=Len(buttonText), Length:=1).Font.Color = vbRed, where buttonText is the full shape text.
Select the target button or shape, then run the macro from Developer > Macros or press F5 in the VBA editor.
Run VBA Shape Text Formatting in WPS Spreadsheets
WPS Spreadsheets can run Excel-compatible VBA macros, so you can open the workbook, access the shape by name, and set Characters(Start:=Len(buttonText), Length:=1) to color only the final check mark.
- 1. Download WPS Office: Download WPS Office from the official website and install WPS Spreadsheets.
- 2. Open the workbook: Open the .xlsm or .xlsx file in WPS Spreadsheets and enable macros if prompted.
- 3. Open the VBA editor: Press Alt+F11 to open the VBA editor, then insert a new module.
- 4. Run the corrected macro: Paste the VBA code that sets Characters(Start:=Len(buttonText), Length:=1).Font.Color = vbRed and run it from Developer > Macros.
- 5. Verify the shape text: Check the shape or button to confirm that only the final check mark changed color.
Frequently Asked Questions
Why does Start:=Len(buttonText)-1 color the wrong character?
VBA Characters indexing starts at 1. If the check mark is the last character, its position is Len(buttonText). Starting at Len(buttonText)-1 selects the character before the final check mark.
Can I use this VBA macro in WPS Office?
Yes. WPS Spreadsheets supports Excel-compatible VBA macros. Open the workbook in WPS Spreadsheets, press Alt+F11, and run the same TextFrame.Characters formatting code.
How do I limit the color change to only one character?
Set Length:=1 in the Characters method. This applies the font color only to the single character at the Start position.
What if my shape name contains spaces?
Use the exact shape name as a string, such as Array("Check Button"), or assign the button name to the strButtonName variable before passing it to Shapes.Range.
Can I change the last character to a color other than red?
Yes. Replace vbRed with another VBA color constant, such as vbBlue or vbGreen, or use RGB(0,128,0) for a custom color.
