VBA & Macro Problems

How to Restore Excel Conditional Formatting Using Office Scripts

Muhammad Talha Oct 10, 2026 868 views

Question details

The user needs to write an Office Script to reapply conditional formatting to an XLSX file because an automated Power Automate flow is clearing the existing formatting rules.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating workbook updates via Power Automate without permanently losing the conditional formatting rules in a standard XLSX file.
Observed behavior: The Power Automate flow clears all existing conditional formatting. The user needs to automatically restore row highlighting based on a specific text value entered in column A.

Before you start

Before creating and running your script, make sure to test it on a backup copy of your XLSX workbook to prevent accidental data or formatting loss. Verify that your Microsoft 365 account has permissions to create and execute Office Scripts.

Solution 1Recommended

Create an Office Script to Reapply Conditional Formatting

Write a custom Office Script that targets your table range, removes obsolete rules, and applies a custom formula-based conditional formatting rule to highlight the full row.

Because the file must remain in the XLSX format, VBA macros cannot be used. Office Scripts provide a lightweight, cloud-friendly alternative that integrates perfectly with Power Automate flows to reapply cell formatting automatically.

Open the Automate Tab

Open your workbook in Excel on the web or the Excel desktop application. Navigate to the 'Automate' tab on the ribbon and select 'New Script'.

Target the Table Range

In the Code Editor, define your target range. Use a command like `let myTable = workbook.getTable("Table1");` and `let range = myTable.getRangeBetweenHeaderAndTotal();` to select the data body range.

Clear Existing Formatting

Before applying new rules, clear any broken rules left behind by the flow by adding `range.clearAllConditionalFormats();` to your script.

Add the Custom Formula Rule

Apply the new rule using a custom formula that checks column A. For example, use `let condFormat = range.addConditionalFormat(ExcelScript.ConditionalFormatType.custom);` and set the formula to `=$A2="r"` to highlight the row.

Integrate with Power Automate

Save your script. Open your Power Automate flow, add a 'Run script' action for Excel Online, and select the script you just created to run after the workbook updates.

Create an Office Script to Reapply Conditional Formatting

Testing Your Script: Always run the script manually within Excel to confirm the row colors are applied correctly before handing it over to your Power Automate flow.

Advanced Spreadsheet Automation

Automate Conditional Formatting with WPS Spreadsheet

WPS Office provides powerful built-in JS Macros and robust Conditional Formatting features to automate your data highlighting efficiently without relying on complex cloud-only flows.

1. Open Your Workbook: Launch WPS Spreadsheet and open your .xlsx file.
2. Configure Conditional Formatting: Navigate to the Home tab and click 'Conditional Formatting' to visually configure your row-highlighting rules without needing code.
3. Access JS Macros: If automation is required, go to the Tools tab and select 'JS Macro' to open the built-in code editor.
4. Run Local Scripts: Write JavaScript to format your target ranges dynamically and execute it locally without needing third-party cloud integrations.

Fully compatible with Microsoft Excel (.xlsx) formats and standard conditional formatting rules.Supports powerful local JavaScript-based Macros (WPS JS) for custom automation.Lightweight, fast execution for large datasets.Free to download with an intuitive interface similar to Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Automate clear my Excel conditional formatting?

Certain data manipulation actions in Power Automate, such as replacing a file, clearing tables, or adding large arrays of data, can overwrite existing cell metadata. Reapplying the rules via an Office Script ensures your formatting remains intact.

Can I use VBA instead of an Office Script for this automation?

If your workflow dictates that the file must remain in the standard .xlsx format, you cannot use VBA because VBA requires saving the file as an .xlsm macro-enabled workbook. Office Scripts are the standard solution for .xlsx files in cloud workflows.

How do I format an entire row instead of a single cell using an Office Script?

To format an entire row, ensure your conditional formatting rule applies to the entire table range (e.g., A2:R4210) and use an absolute column reference in your custom formula, such as =$A2="r", locking the column but allowing the row to remain relative.

Excel & Data Problems