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.
