VBA & Macro Problems

How to Update Excel Conditional Formatting Applies To Range Using VBA

Jason Miller Oct 21, 2026 868 views

Question details

The user needs to modify the 'Applies To' range of an existing conditional formatting rule using VBA because the macro recorder ignores this specific action.

How to Update Excel Conditional Formatting Applies To Range Using VBA

Product: Excel
Device & OS: not provided
Scenario: A user is attempting to record a macro to automate adjustments to an existing conditional formatting rule's range but finds the generated script incomplete.
Observed behavior: The Excel macro recorder successfully records the creation of conditional formatting rules but fails to capture any modifications made exclusively to the 'Applies To' range.

Before you start

Before modifying VBA scripts, ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and identify the correct index number for the specific conditional formatting rule you want to target.

Solution 1Recommended

Use the VBA ModifyAppliesToRange Method

Directly target the conditional formatting rule index and apply the ModifyAppliesToRange method to force the range update.

Since the Excel Macro Recorder inherently ignores changes made to the 'Applies To' field of a format condition, you must write a specific line of VBA code to execute the change.

You need to specify a cell that currently holds the conditional formatting rule, identify the rule's index, and then declare the new range.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert or Access a Module

Locate your workbook in the Project Explorer panel. Right-click it and select Insert > Module, or double-click an existing module where you want to add the code.

Write the Update Syntax

Create a new Sub procedure and input the specific ModifyAppliesToRange command. For example, to change the first rule on cell L3 to apply to A3:D100, write: Range("L3").FormatConditions(1).ModifyAppliesToRange Range("A3:D100")

Run the Macro

Click anywhere inside your new code block and press F5 to run the macro, or assign it to a shape or button in your worksheet for everyday use.

Identifying the Correct Index: The number in FormatConditions(1) represents the priority or creation order of the rule on that cell. If the cell has multiple rules, you may need to change '1' to '2', '3', etc., to target the correct formatting rule.

Advanced Spreadsheet Features

Manage Macros and Conditional Formatting Seamlessly with WPS Spreadsheets

WPS Office offers a fully compatible spreadsheet environment where you can easily configure advanced conditional formatting rules and automate your workflows using standard VBA macros.

1. Download and Install WPS Office: Install WPS Office and open your macro-enabled spreadsheet in WPS Spreadsheets.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on the 'VBA Editor' icon.
3. Apply the Range Modification Code: Use the exact same VBA ModifyAppliesToRange syntax to update your conditional formatting rules dynamically.
4. Save with Macro Compatibility: Save your file as a Macro-Enabled Spreadsheet (.xlsm) to ensure full compatibility with Microsoft Office users.

Fully compatible with Microsoft Excel VBA syntax and .xlsm formats.Advanced and highly visual conditional formatting rules manager.Lightweight application that runs heavy scripts quickly.Free and intuitive interface tailored for complex data processing.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the macro recorder capture changes to the Applies To range?

The built-in macro recorder is designed to log changes to the actual logic or styling of a conditional format. Adjusting only the reference range (the 'Applies To' field) is an interface-level change that the recorder's engine inherently ignores, necessitating manual VBA adjustment.

How can I set the conditional formatting range dynamically based on data length?

You can replace the hardcoded range string with a dynamic variable. First, find the last used row using a variable like lastRow = Cells(Rows.Count, 1).End(xlUp).Row. Then, update the range logic to: Range("L3").FormatConditions(1).ModifyAppliesToRange Range("A3:D" & lastRow).

What happens if I specify the wrong FormatConditions index in my VBA code?

If you target the wrong index, VBA will update the range for an entirely different conditional formatting rule applied to that cell. If you specify an index number that exceeds the total number of rules on the targeted cell, VBA will return a 'Subscript out of range' runtime error.

Excel & Data Problems