VBA Conditional Formatting

We can apply conditional formatting to a cell or range of cells in Excel. A conditional format is a format that is applied only to cells that meet certain criteria, say values above a particular value, positive or negative values, or values with a particular formula, etc. This conditional formatting can also be done in Excel VBA programming using the 'FormatConditions collection' macro/procedure.

The FormatConditions represents a conditional format that one can set by calling a method that returns a variable of that type. It contains all conditional formats for a single range and can hold only three format conditions.

FormatConditions.Add/Modify/Delete is used in VBA to add/modify/delete FormatCondition objects to the collection. A FormatCondition object represents each format. FormatConditions is a property of the Range object, and Add the following parameters with the below syntax:

FormatConditions.Add (Type, Operator, Formula1, Formula2) 

The Add formula syntax has the following arguments:

• Type: Required. It represents the conditional format based on the value present in the cell or an expression.
• Operator: Optional. It represents the operator's value when 'Type' is based on cell value.
• Formula1: Optional. It represents the value or expression associated with the conditional format.
• Formula2: Optional. It represents the value or expression associated with the second part of the conditional format when the parameter: 'Operator' is either 'xlBetween' or 'xlNotBetween.'

FormatConditions.Modify also has the same syntax as FormatConditions.Add.

Following is the list of some values/enumerations that some parameters of 'Add’/’Modify' can take:

Below are examples of conditional formatting in Excel VBA.

Example #1

We have an Excel file containing some students' names and marks. We wish to determine/highlight the marks as "Bold" and "blue," which are greater than 80. "Bold" and "Red," which is less than 50. Let us see the data contained in the file:

We use the FormatConditions.Add the function below to accomplish this:

• Go to Developer -> Visual Basic Editor:

• Right-click on the workbook name in the ‘Project-VBAProject’ pane-> ‘Insert’-> ‘Module.’

• Now write the code/procedure in this module:

Code:

Sub formatting()

End Sub

• Define the variable rng, condition1, condition2:

Code:

Sub formatting()

  Dim rng As Range
  Dim condition1 As FormatCondition, condition2 As FormatCondition

End Sub

• Set/fix the range on which the conditional formatting needs using the VBA ‘Range’ function:

Code:

Sub formatting()

  Dim rng As Range
  Dim condition1 As FormatCondition, condition2 As FormatCondition

  Set rng = Range("B2", "B11")

End Sub

• Delete/clear any existing conditional formatting (if any) from the range, using 'FormatConditions.Delete':

Code:

Sub formatting()

  Dim rng As Range
  Dim condition1 As FormatCondition, condition2 As FormatCondition

  Set rng = Range("B2", "B11")

  rng.FormatConditions.Delete

End Sub

• Now, define and set the criteria for each conditional format, using 'FormatConditions.Add':

Code:

Sub formatting()

  Dim rng As Range
  Dim condition1 As FormatCondition, condition2 As FormatCondition

  Set rng = Range("B2", "B11")

  rng.FormatConditions.Delete

  Set condition1 = rng.FormatConditions.Add(xlCellValue, xlGreater, "=80")
  Set condition2 = rng.FormatConditions.Add(xlCellValue, xlLess, "=50")

End Sub

• Define and set the format to be applied for each condition.

Copy and paste this code into your VBA class module.

Code:

Sub formatting()

  'Definining the variables:
  Dim rng As Range
  Dim condition1 As FormatCondition, condition2 As FormatCondition

 'Fixing/Setting the range on which conditional formatting is to be desired
  Set rng = Range("B2", "B11")

  'To delete/clear any existing conditional formatting from the range
   rng.FormatConditions.Delete

  'Defining and setting the criteria for each conditional format
   Set condition1 = rng.FormatConditions.Add(xlCellValue, xlGreater, "=80")
   Set condition2 = rng.FormatConditions.Add(xlCellValue, xlLess, "=50")

   'Defining and setting the format to be applied for each condition
   With condition1
    .Font.Color = vbBlue
    .Font.Bold = True
   End With

   With condition2
     .Font.Color = vbRed
      .Font.Bold = True
   End With

End Sub

Now, when we run this code using the F5 key or manually, we see that the marks that are less than 50 get highlighted in bold and red, while those that are greater than 80 get highlighted in bold and blue as follows:

Note: Some of the properties for the appearance of formatted cells that can we can use with FormatCondition are:

Example #2

Let's say in the above example. We have another column that states that the student is a 'Topper' if they score more than 80. Else, written Pass/Fail against them. Now, we wish to highlight the values stated as 'Topper' as "Bold" and "Blue." Let us see the data contained in the file:

In this case, the code/procedure would work as follows:

Code:

Sub TextFormatting()

End Sub

Define and set the format to be applied for each condition

Code:

Sub TextFormatting()

With Range("c2:c11").FormatConditions.Add(xlTextString, TextOperator:=xlContains, String:="topper")
With .Font
.Bold = True
.Color = vbBlue
End With
End With

End Sub

We can see in the above code that we wish to test if the range: 'C2:C11" contains the string: "Topper," so the parameter: "Onamestor" of 'Format.Add' takes the enumeration:" Xcontains" to test this condition in the fixed range (i.e., C2:C11). Then, do the required conditional formatting (font changes) on this range.

Now when we run this code manually or by pressing the F5 key, we see that cell values with 'Topper' get highlighted in blue and bold:

Note: In the above two examples, we have seen how the 'Add' method works in case of any cell value criteria (numeric or text string).

Below are some other instances/criteria that we can  use to test and thus apply VBA conditional formatting:

• Format by Time Period
• Average condition
• Colour Scale condition
• IconSet condition
• Databar condition
• Unique Values
• Duplicate Values
• Top10 values
• Percentile Condition
• Blanks Condition, etc.

With different conditions to test, different values/enumerations taken by parameters of ‘Add.’

• We can use the 'Add' method with 'FormatConditions' to create a new conditional format, the 'Delete' method to delete any conditional format, and the 'Modify' method to alter any existing conditional format.
• The 'Add' method with 'FormatConditions Collection' fails if we create more than three conditional formats for a single range.
• To apply more than three conditional formats to a range using the 'Add' method, we can use 'If' or 'select case.'
• If the 'Add' method has its 'Type' parameter as: 'xlExpression,' then the parameter 'Operator' is ignored.
• The parameters: 'Formula1' and 'Formula2' in the 'Add' method can be a cell reference, constant value, string value, or even a formula.
• The parameter: 'Formula2' is used only when the parameter: 'Operator' is either 'xlBetween' or 'xlNotBetween,' or else it is ignored.
• To remove all the conditional formatting from any worksheet, we can use the 'Delete' method as follows:

Cells.FormatConditions.Delete