VBA & Macro Problems

How to Shade Table Cells Based on Values Using Word VBA Macros

Elise Williams Oct 10, 2026 869 views

Question details

The user needs a Word VBA macro to conditionally shade table cells based on specific text values, specifically turning cells containing 'Yes' yellow and 'No' red.

How to Shade Table Cells Based on Values Using Word VBA Macros

Product: Word
Device & OS: not provided
Scenario: Applying conditional formatting to a Word table to visually highlight specific data points based on the text they contain.
Observed behavior: Word lacks built-in conditional formatting for native tables, requiring a VBA macro to read cell contents and apply background colors programmatically.

Before you start

Running macros can permanently alter document formatting in ways that cannot be undone via the standard Undo button, so save a backup copy of your Word document before proceeding.

Solution 1Recommended

Create and Run a VBA Macro to Conditionally Shade Cells

Use a custom VBA macro to iterate through table cells, evaluate the text, and apply specific background colors based on your conditions.

Since Word does not have native conditional formatting for tables like Excel does, VBA (Visual Basic for Applications) is required to automate the process. The macro must loop through each cell, clean the text string, and apply the Shading.BackgroundPatternColor.

Open the VBA Editor

Press ALT + F11 on your keyboard to launch the Microsoft Visual Basic for Applications window.

Insert a New Module

In the top menu, click Insert and select Module to create a blank workspace for your macro code.

Write the Loop and Clean the Text

Write a For Each loop to iterate through the table's cells. Inside the loop, extract the cell text and remove the end-of-cell marker (ASCII characters 13 and 7) using the Left() function, as Word adds these hidden characters to every cell.

Apply Conditional Shading

Add an If or Select Case statement. For the 'Yes' condition, set Cell.Shading.BackgroundPatternColor = wdColorYellow. For the 'No' condition, set it to wdColorRed.

Run the Macro

Close the VBA Editor. In your Word document, click inside the target table, press ALT + F8 to open the Macros dialog, select your newly created macro, and click Run.

Create and Run a VBA Macro to Conditionally Shade Cells

End-of-Cell Markers: Word table cells contain hidden characters (end-of-cell markers) that must be stripped using VBA string functions. If you do not remove them, comparing the cell text to 'Yes' or 'No' will always result in a mismatch.

Solution 2

Use an Embedded Excel Spreadsheet for Native Conditional Formatting

If your table requires advanced or frequently updated conditional formatting, embedding an Excel spreadsheet is much easier than maintaining Word macros.

Efficient Document Formatting

Handle Advanced Tables and Macros Seamlessly in WPS Office

WPS Office provides robust support for document formatting, advanced tables, and VBA macros (available in the professional version), allowing you to automate repetitive tasks and seamlessly embed spreadsheets without slowing down your workflow.

1. Enable the Developer Tab: Open your document in WPS Writer and navigate to the Developer tab to access the macro tools.
2. Launch the VBA Editor: Click on the Macros button or Visual Basic editor icon to input your VBA code for cell shading.
3. Use Embedded Excel Alternately: If you prefer not to code, go to the Insert tab, click Table, and choose Excel Spreadsheet to use standard conditional formatting.

Fully compatible with Microsoft Word .docx and .docm (macro-enabled) formatsSupports embedding Excel tables directly into Writer documents for native conditional formattingFamiliar ribbon interface makes finding Developer tools and macro settings intuitiveLightweight application that loads heavy tables and complex macros quickly

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro fail to recognize the text inside the Word table cell?

Word table cells include a hidden end-of-cell marker consisting of a carriage return and a bell character. If your macro does not use a function to strip these characters from the string before comparing it to 'Yes' or 'No', the conditional check will return false and the shading will not be applied.

Can I use Excel's conditional formatting directly on native Word tables?

No, Word does not feature native conditional formatting for its standard tables. To utilize standard conditional formatting rules without writing VBA code, you must insert an embedded Excel spreadsheet via the Insert > Table menu.

How do I change the macro to highlight a different word or phrase?

Open the VBA Editor by pressing ALT + F11, locate the If or Select Case statement in your macro code, and replace 'Yes' or 'No' with your new target word. Ensure that the text casing matches perfectly, or use the UCase() function in your code to make the comparison case-insensitive.

Excel & Data Problems