Скачать или смотреть How to Use VBA for Conditional Formatting Based on Cell Values

Learn how to automate cell formatting in `VBA` by conditionally formatting a range based on the value of another cell. Turn text grey when a certain condition is met!
---
This video is based on the question https://stackoverflow.com/q/68461419/ asked by the user 'DiYage' ( https://stackoverflow.com/u/8092758/ ) and on the answer https://stackoverflow.com/a/68461458/ provided by the user 'Tim Williams' ( https://stackoverflow.com/u/478884/ ) 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: VBA How do I conditionally format a range of cells based on the value of one cell in that range?

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.
---
Conditional Formatting in Excel using VBA

If you're working with Excel and need to highlight specific rows based on the value of a certain cell, you might have stumbled upon the challenge of using Visual Basic for Applications (VBA) to automate this process. Specifically, today, we'll discuss how to conditionally format a range of cells based on the value of a single cell in that row.

The Problem Explained

As an example, let’s say you want the text of an entire row to turn grey if a specific cell in that row contains "GAT1".
You've probably found that while Excel provides built-in conditional formatting options for this, you may run into issues when trying to automate the process via VBA.

The challenge arises when your formatting criteria is based on a formula that includes a string. In VBA, you typically need to enclose string values in quotes, but this can lead to confusion as the criteria itself is also enclosed in quotes. As the user pointed out, the syntax can become tricky when trying to combine quotes within VBA code.

The Solution Breakdown

To tackle this issue, let's break down the solution into simpler steps. Here’s how you can set up your conditional formatting using VBA:

Code Implementation

Declare Your Range:
Start by defining the range you want to apply the formatting to. In this case, we are targeting A1:P200.

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

Add the Conditional Formatting:
Use the FormatConditions.Add method to implement your conditional formatting. Here’s where you have to be clever with quotes.

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

Explanation of the Code

Type:=xlExpression: This tells Excel that you’re using an expression to define your formatting conditions.

Formula1:="=$F1=""GAT1""":

The = sign is used to evaluate the condition.

"" is used to escape the double quotes around GAT1. Since we're already within a string that's surrounded by quotes, we need to use double quotes to denote a literal quote character.

Result

Once you run this code, any row within your specified range where the cell in column F contains "GAT1" will have its text color changed to grey.

Conclusion

This technique allows you to apply conditional formatting to your Excel sheets programmatically using VBA. With this knowledge, you can not only enhance the visual appeal of your data but also make it more organized and easier to interpret.

If you run into any issues, remember to double-check your syntax, especially when dealing with strings and quotes. Happy coding!