How to Fix an Invalid Excel Office Script Conditional Formatting Formula
Question details
The user needs to correct syntax errors caused by improper quotation marks in an Office Script formula used to apply conditional formatting.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using an Office Script to automatically apply conditional formatting to column A, specifically highlighting cells where the final digit is 2 or 7.
- Observed behavior
- The Office Script fails or generates an invalid formula error because of excess or unescaped quotation marks within the formula string.
Verify the target range for your conditional formatting. Ensure the relative cell reference in your formula exactly matches the first row of your selected range (e.g., A1) to prevent the rule from shifting improperly.
Correct Quotation Marks in the Office Script Formula
Adjust the quotation marks within the setFormula string to prevent syntax errors and ensure the script executes properly.
When passing formulas into Office Scripts using strings, nested quotation marks often cause the script to break. You must either use single quotes for the outer JavaScript string or escape the internal double quotes using backslashes.
Navigate to the Automate tab in your Excel workbook and open the target script in the Code Editor.
Modify the conditional formatting rule line to enclose the entire formula in single quotes. Replace the statement with: conditionalFormatting.getCustom().getRule().setFormula('=OR(RIGHT(A1,1)="2",RIGHT(A1,1)="7")');
If you prefer or require double quotes for the outer string, escape the internal quotes with backslashes. The syntax should look like this: "=OR(RIGHT(A1,1)=\"2\",RIGHT(A1,1)=\"7\")"
Save the changes in the Code Editor and run the script. Verify that the correct cells in column A are highlighted based on the last digit.

Automate Spreadsheets with WPS Office
While Microsoft Excel relies on Office Scripts for web automation, WPS Spreadsheet offers robust support for advanced data formatting, JavaScript macros, and VBA. Enjoy a powerful spreadsheet application without expensive subscriptions.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
- 2. Open your Excel File: Launch WPS Spreadsheet and securely open your existing .xlsx workbooks.
- 3. Apply Conditional Formatting: Use the intuitive Conditional Formatting menu under the Home tab to easily highlight data.

Frequently Asked Questions
Why do double quotes break my Office Script formulas?
In TypeScript and JavaScript (which Office Scripts use), strings are enclosed in quotes. If you use unescaped double quotes inside a double-quoted string, the script misinterprets where the string ends, causing an unexpected token or syntax error.
How do I highlight cells based on their last digit in Excel?
You can use the RIGHT function inside a conditional formatting rule. To check if a cell's value ends in '2' or '7', select your range and input the custom formula: =OR(RIGHT(A1,1)="2",RIGHT(A1,1)="7").
Can I use VBA instead of Office Scripts to set this up?
Yes. VBA uses FormatConditions.Add to apply formatting. When writing the formula string in VBA, you escape double quotes by doubling them up, such as "=OR(RIGHT(A1,1)=""2"",RIGHT(A1,1)=""7"")".
Why is my conditional formatting applied to the wrong cells via script?
This commonly happens when the cell reference in your formula (like A1) does not correspond to the first row or column of the range you are applying the formatting to. Ensure your formula dynamically aligns with your target range.




