How to use FormatConditions VBA to add conditional formatting to a range in Excel

Who I am
Pavel Yosifovich
@pavelyosifovich
Author and references

In case it is necessary to create a dynamism to allow the distinction of one piece of information from another, it is possible through Visual Basic.

The coding program allows the creator to establish all kinds of rules and styles for that content that conforms to the content of the parameters.

What is conditional formatting and what is its function?

As mentioned above, the conditional format it is used to analyze each of the data displayed to identify any established trends.



Adding conditional formatting to a range of a table, to one or more columns, allows you to highlight the set of cells that need to be highlighted.

This way it is possible highlight unusual values or view data containing variations through visual changes.

Method of operation

The coding FormatConditions it is responsible for changing the appearance of one or more cells if it satisfies the established conditions, resulting in true or false.

 

The group of conditions is set by default by the program, or it allows you to create your own condition in case you can't find the one you need.

The changes made to the cell range from changing the color of the text, to the borders, to the highlighting of the content, to the cell color change, to the addition of symbols, among others.

Sample code to add conditional formatting to a range using FormatConditions

In the example shown below, the FormatConditions function is entered for the column of a monetary income table.

Its purpose is edit the text and color of the cell for data that has or exceeds a specific value inserted within the text.



Subconditional ()

With worksheets (1) .Range ("A2: A30) .FormatConditions _

.Add (Type: = "xlCellValue", Operator: = "xlGreaterEqual, _

Formula 1: = 15000)

Con.Font

.bold = true

Color Index = 7

Ends with

Ends with

With worksheets (1) .Range ("A2: A30"). FormatConditions _

.Add (Type: = "xlCellValue", Operator: = "xlEqual, _

Formula 1: = 12000)

With Edges

.LineStyle = xlContinuo

Color Index = 5

Ends with

Con.Internal

Color Index = 4

Ends with

Ends with

Through this macro code, by defining from top to bottom:

  • A subfunction conditional call that sets a conditional format on the range of cell A, from number two to number 30, of the first sheet.
  • The instrument FormatConditions to satisfy the established rules of «.Add (Type, Operator, Formula1, Formula2)» to set the parameters.
    Among these trends we read that the type is the value of the cell, the operator is greater than or equal to, and the formula is the parameter of those data that are greater than or equal to 15000.

 


  • La distinction that will receive those cells that have i true data, the text of which will become bold and change to a purple color.
  • As second condition the same code as the beginning appears, unlike now the operator is the same and the selected parameter is that data whose value is equal to 12000.
  • Once again, the distinctions that will receive the true values ​​will be a color change of the border and interior of the cell.
  • Finally, all blocks of the condition format are closed.


In this way, when you save the module you created with this function, you will only have to execute it via the button » Macro ". Excel macros are created by the VBA program.

In case you didn't know, a macro is an advanced Excel function that allows you to record a series of steps that are used repeatedly. This sequence of steps can be used for tasks such as copying and pasting cells, counting cells, or creating charts.

add a comment of How to use FormatConditions VBA to add conditional formatting to a range in Excel
Comment sent successfully! We will review it in the next few hours.