Excel Conditional Formatting

Excel Conditional Formatting allows you lớn define rules which determine cell formatting.

Bạn đang xem: Use conditional formatting to highlight information

For example, you can create a rule that highlights cells that meet certain criteria. Examples include:

Numbers that fall within a certain range (ex. Less than 0).The đứng đầu 10 items in a list.Creating a “heat map”.“Formula-based” rules for virtually any conditional formatting.

In Excel, Conditional Formatting can be found in the Ribbon under Home > Styles (ALT > H > L).

*

To create your own rule, click on ‘New Rule’ và a new window will appear:

*

Conditional Formatting in VBA

All of these Conditional Formatting features can be accessed using VBA.

Note that when you mix up conditional formatting from within VBA code, your new parameters will appear in the Excel front-end conditional formatting window và will be visible to the user. The user will be able to edit or delete these unless you have locked the worksheet.

The conditional formatting rules are also saved when the worksheet is saved

Conditional formatting rules apply specifically lớn a particular worksheet and to a particular range of cells. If they are needed elsewhere in the workbook, then they must be mix up on that worksheet as well.


Practical Uses of Conditional Formatting in VBA

You may have a large chunk of raw data imported into your worksheet from a CSV (comma-separated values) file, or from a database table or query. This may flow through into a dashboard or report, with changing numbers imported from one period khổng lồ another.

Where a number changes & is outside an acceptable range, you may want lớn highlight this e.g. Background color of the cell in red, & you can vị this setting up conditional formatting. In this way, the user will be instantly drawn to lớn this number, & can then investigate why this is happening.

You can use VBA to lớn turn the conditional formatting on or off. You can use VBA to lớn clear the rules on a range of cells, or turn them back on again. There may be a situation where there is a perfectly good reason for an unusual number, but when the user presents the dashboard or report to a higher level of management, they want to be able to remove the ‘alarm bells’.

Also, on the raw imported data, you may want khổng lồ highlight where numbers are ridiculously large or ridiculously small. The imported data range is usually a different form size for each period, so you can use VBA lớn evaluate the size of the new range of data & insert conditional formatting only for that range.

You may also have a situation where there is a sorted menu of names with numeric values against each one e.g. Employee salary, exam marks. With conditional formatting, you can use graduated colors lớn go from highest lớn lowest, which looks very impressive for presentation purposes.

However, the menu of names will not always be static in size, and you can use VBA code to lớn refresh the scale of graduated colors according khổng lồ changes in the form size of the range.

A Simple Example of Creating a Conditional Format on a Range

This example sets up conditional formatting for a range of cells (A1:A10) on a worksheet. If the number in the range is between 100 và 150 then the cell background color will be red, otherwise it will have no color.

Sub ConditionalFormattingExample()‘Define RangeDim MyRange As RangeSet MyRange = Range(“A1:A10”)‘Delete Existing Conditional Formatting from RangeMyRange.FormatConditions.Delete‘Apply Conditional FormattingMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="=100", Formula2:="=150"MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)End SubNotice that first we define the range MyRange to apply conditional formatting.

Xem thêm: Rau Câu Thường Được Dùng Để Làm Gì, Ăn Rau Câu Có Tác Dụng Gì

Next we delete any existing conditional formatting for the range. This is a good idea khổng lồ prevent the same rule from being added each time the code is ran (of course it won’t be appropriate in all circumstances).

Colors are given by numeric values. It is a good idea to use RGB (Red, Green, Blue) notation for this. You can use standard color constants for this e.g. VbRed, vbBlue, but you are limited to lớn eight màu sắc choices.

There are over 16.7M colors available, và using RGB you can access them all. This is far easier than trying to lớn remember which number goes with which color. Each of the three RGB màu sắc number is from 0 lớn 255.

Note that the ‘xlBetween’ parameter is inclusive so cell values of 100 or 150 will satisfy the condition.

Multi-Conditional Formatting

You may want to lớn set up several conditional rules within your data range so that all the values in a range are covered by different conditions:

Sub MultipleConditionalFormattingExample()Dim MyRange As Range"Create range objectSet MyRange = Range(“A1:A10”)"Delete previous conditional formatsMyRange.FormatConditions.Delete"Add first ruleMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="=100", Formula2:="=150"MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)"Add second ruleMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=100"MyRange.FormatConditions(2).Interior.Color = vbBlue"Add third ruleMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="=150"MyRange.FormatConditions(3).Interior.Color = vbYellowEnd SubThis example sets up the first rule as before, with the cell color of red if the cell value is between 100 & 150.

Two more rules are then added. If the cell value is less than 100, then the cell màu sắc is blue, & if it is greater than 150, then the cell màu sắc is yellow.

In this example, you need lớn ensure that all possibilities of numbers are covered, and that the rules vì chưng not overlap.

If blank cells are in this range, then they will show as blue, because Excel still takes them as having a value less than 100.

The way around this is to địa chỉ in another condition as an expression. This needs khổng lồ be added as the first condition rule within the code. It is very important where there are multiple rules, to get the order of execution right otherwise results may be unpredictable.

Sub MultipleConditionalFormattingExample()Dim MyRange As Range"Create range objectSet MyRange = Range(“A1:A10”)"Delete previous conditional formatsMyRange.FormatConditions.Delete"Add first ruleMyRange.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=LEN(TRIM(A1))=0"MyRange.FormatConditions(1).Interior.Pattern = xlNone"Add second ruleMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="=100", Formula2:="=150"MyRange.FormatConditions(2).Interior.Color = RGB(255, 0, 0)"Add third ruleMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=100"MyRange.FormatConditions(3).Interior.Color = vbBlue"Add fourth ruleMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="=150"MyRange.FormatConditions(4).Interior.Color = RGB(0, 255, 0)End SubThis uses the type of xlExpression, và then uses a standard Excel formula khổng lồ determine if a cell is blank instead of a numeric value.

The FormatConditions object is part of the Range object. It acts in the same way as a collection with the index starting at 1. You can iterate through this object using a For…Next or For…Each loop.

Deleting a Rule

Sometimes, you may need to lớn delete an individual rule in a mix of multiple rules if it does not suit the data requirements.

Sub DeleteConditionalFormattingExample()Dim MyRange As Range"Create range objectSet MyRange = Range(“A1:A10”)"Delete previous conditional formatsMyRange.FormatConditions.Delete"Add first ruleMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="=100", Formula2:="=150" MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)"Delete ruleMyRange.FormatConditions(1).DeleteEnd SubThis code creates a new rule for range A1:A10, & then deletes it. You must use the correct index number for the deletion, so check on ‘Manage Rules’ on the Excel front-end (this will show the rules in order of execution) to ensure that you get the correct index number. Lưu ý that there is no undo facility in Excel if you delete a conditional formatting rule in VBA, unlike if you vị it through the Excel front-end.