VBA & Macro Problems

How to Change Excel Shape Colors Based on Text with VBA

Bushra Parveen Oct 19, 2026 870 views

Question details

The user wants to use a VBA macro to dynamically change the fill color of shapes based on specific text keywords contained within them, but encounters syntax errors, runtime errors, and macro blocking issues.

How to Change Excel Shape Colors Based on Text with VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating visual updates in a construction project tracker by changing shape colors when their status text (e.g., Beta, Gamma) changes.
Observed behavior: The macro fails to run, throwing 'Run-time error 5', 'Application-defined or object-defined error', or a warning that Microsoft has blocked macros from an untrusted source. Additionally, a one-line If statement was incorrectly modified into a multiline statement without a closing End If.

Before you start

Ensure that your workbook is saved in a Macro-Enabled format (.xlsm or .xlsb) and that the Developer tab is enabled in your ribbon to access the VBA Editor.

Solution 1Recommended

Fix VBA Syntax with a Multiline If Statement

Use a properly structured multiline If...ElseIf...End If statement to check the text of each shape and apply the corresponding color.

When expanding a one-line If statement to handle multiple conditions, VBA requires that the block is properly closed with an 'End If'. Using the 'InStr' function allows you to search for specific text keywords regardless of other text present in the shape.

Open the VBA Editor

Press 'Alt + F11' to open the Visual Basic for Applications editor. In the Project Explorer, double-click the module containing your code.

Update the VBA Code

Replace your existing loop with the following corrected syntax: Sub ShapeColor() Dim Shp As Shape For Each Shp In Sheets("Sheet2").Shapes If InStr(1, Shp.TextFrame.Characters.Caption, "Beta", vbTextCompare) > 0 Then Shp.Fill.ForeColor.RGB = 15675 ElseIf InStr(1, Shp.TextFrame.Characters.Caption, "Gamma", vbTextCompare) > 0 Then Shp.Fill.ForeColor.RGB = 55675 End If Next Shp End Sub

Run the Macro

Press 'F5' or click the Run button to execute the code. The shapes on 'Sheet2' will now change colors based on the text they contain.

Fix VBA Syntax with a Multiline If Statement

Case Insensitivity: The 'vbTextCompare' argument ensures the search is case-insensitive, meaning 'Beta', 'BETA', and 'beta' will all trigger the color change.

Solution 2

Resolve Run-time Error 5 or Object-Defined Errors

Update the shape text property to 'TextFrame2' if you are working with newer Excel versions or newly created shapes.

Solution 3

Unblock Macros and Add File to Trusted Locations

Fix macro blocking issues by configuring Excel's Trust Center settings for files downloaded from the internet or shared drives.

Efficient VBA Support in WPS Office

Automate Tasks with Macros in WPS Spreadsheet

WPS Spreadsheet provides excellent support for VBA macros, allowing you to seamlessly run your automated scripts, such as changing shape colors based on text. With high compatibility and a familiar developer environment, you can manage your construction trackers and complex workflows effortlessly.

1. Enable the Developer Tab: Open WPS Spreadsheet, go to Options, and enable the Developer tools to access macro settings.
2. Open the VBA Editor: Click 'Developer' > 'Visual Basic Editor' (or press Alt + F11) to paste or modify your shape color macro.
3. Run Your Automation: Execute your script directly within WPS to update shape styles dynamically based on your text inputs.

Full compatibility with Microsoft Excel VBA macro codeNative support for .xlsm and .xlsb macro-enabled formatsBuilt-in VBA editor with familiar debugging toolsLightweight software that processes heavy data sheets faster

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get 'Compile Error: End If without block If' in VBA?

This error occurs when your VBA code contains an 'End If' statement without a matching multi-line 'If' statement. If you write an 'If...Then' condition entirely on a single line, VBA treats it as a complete statement and does not require an 'End If'. To fix this, either remove the 'End If' or format the 'If' statement over multiple lines.

What is the difference between TextFrame and TextFrame2 in Excel VBA?

'TextFrame' is the older object model used in earlier versions of Excel, while 'TextFrame2' was introduced in Excel 2007 to support advanced text formatting features (like 3D effects, shadows, and newer shape styles). If a macro fails on newer shapes with 'Run-time error 5', switching to 'TextFrame2.TextRange.Text' usually resolves the issue.

How do I find the correct RGB color code for my shapes?

You can find a color's RGB value in Excel by selecting a shape, clicking 'Shape Fill' > 'More Fill Colors...', and navigating to the 'Custom' tab. To use it in VBA, use the RGB function: Shp.Fill.ForeColor.RGB = RGB(255, 0, 0) for red.

Can I run my existing Excel macros in WPS Office?

Yes, the Professional and Premium versions of WPS Office fully support VBA macros. You can open your .xlsm files directly in WPS Spreadsheet, and your existing VBA code will function smoothly thanks to its high compatibility with Microsoft Office's macro engine.

Excel & Data Problems