Скачать или смотреть How to Reuse Excel Conditional Formatting Across Columns with VBA

Learn how to easily apply the same conditional formatting rules to multiple columns in Excel using VBA. This guide provides actionable steps to create reusable rules without specific cell references.
---
This video is based on the question https://stackoverflow.com/q/74510237/ asked by the user 'Dermot Bohan' ( https://stackoverflow.com/u/7107774/ ) and on the answer https://stackoverflow.com/a/74510565/ provided by the user 'Red Hare' ( https://stackoverflow.com/u/19618751/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Can I remove a reference to a specific cell in a rule containing a formula so I can reuse it across multiple columns?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Reuse Excel Conditional Formatting Across Columns with VBA

When working in Excel, you may find yourself in a situation where you want to apply the same formatting rule across multiple columns. For example, you may want to highlight cells in green if they are empty. This task can be tricky if you're creating the rules manually, especially if they are tied to specific cell references.

In this guide, we'll explore how to create a versatile VBA macro that allows you to reuse conditional formatting rules without being restricted to a specific cell reference.

The Problem

Imagine you have a set of columns where you need to apply the same rule: filling empty cells with a green color. Originally, you might end up with a rule that specifically references a certain cell, like B2. When you try to apply this rule to other columns, the formatting does not carry over as intended, which causes frustration.

This was your starting point:

[[See Video to Reveal this Text or Code Snippet]]

The Solution

To create a dynamic conditional formatting rule that can adapt to the selected range, follow these steps:

Step 1: Create a Flexible Macro

Instead of hardcoding a cell reference (like B2), we can use a variable to make our code more flexible. Here’s the complete macro that will allow you to apply your desired formatting rule across selected columns:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: How It Works

Dynamic Cell Reference: The line x = Selection.Address(0, 0) captures the address of the selected range relative to its current position, which allows for adaptable referencing.

Condition Application: Selection.FormatConditions.Add is used to add a new conditional formatting rule based on a formula. The formula uses the variable x, which refers to the currently selected range rather than a fixed cell.

Interior Formatting: The code then sets the fill color to green using With Selection.FormatConditions(1).Interior, ensuring that any cells that meet the specified condition are highlighted accordingly.

StopIfTrue: This property is set to False, allowing other conditional formats to evaluate even if this rule is satisfied.

Step 3: Using the Macro

To use this macro, simply select the range of columns where you want the rule applied, and then run the macro. The formatting will fill any empty cells with a green color across all selected columns.

Final Thoughts

By using the modified VBA macro above, you can efficiently apply the same conditional formatting rules across multiple columns in Excel without the hassle of hardcoding cell references. This not only saves time but also keeps your spreadsheet cleaner and more organized.

If you have further questions or need additional help with VBA macros, feel free to reach out! Happy Excelling!