VBA Conditional Formatting

Conditional Formatting in Excel VBA

We can apply conditional formatting to a cell or range of cells in Excel. A conditional format is a format which 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 ‘Format Conditions Collection’ in the macro/procedure.

Format Condition is used to represent a conditional format that can be 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. Each format is represented by a FormatCondition object. FormatConditions is a property of the Range object and Add has following parameters with below syntax:

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

The Add formula syntax has following arguments:

• Type: Required, represents if the conditional format is based on value present in the cell or an expression
• Operator: Optional, represents the operator to be used with a value when ‘Type’ is based on cell value
• Formula1: Optional, represents the value or expression associated with the conditional format.
• Formula2: Optional, represents the value or expression associated with the second part of 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/enumeration that can be taken by some parameters of ‘Add’/’Modify’:

Examples of VBA Conditional Formatting

Below are the examples of Conditional formatting in excel vba.

Example #1

Let us say we have an Excel file containing some students’ name and marks, and we wish to determine/highlight the marks as Bold and blue in color which is greater than 80, and as Bold and Red in color which is less than 50. Let us see the data contained in the file:

We use the FormatConditions.Add function as 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 conditional formatting is to be desired 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 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:

Example #2

Let’s say in the above example we have another column also which states that the student is a ‘Topper’ if he/she scores more than 80 marks, else Pass/Fail is written 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: “Operator” of ‘Format.Add’ takes the enumeration:”xlContains”, to test this condition in the fixed range (i.e C2:C11), and 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:

Below are some other instances/criteria that can be used to test and thus apply VBA conditional formatting on:

• 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 be tested, different values/enumeration are taken by parameters of ‘Add’.

Things to Remember About VBA Conditional Formatting

• ‘Add’ method with ‘FormatConditions’ is used to create a new conditional format, ’Delete’ method to delete any conditional format, and ‘Modify’ method to alter any existing conditional format.
• The ‘Add’ method with ‘FormatConditions Collection’ fails if more than three conditional formats are created 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’, else it is ignored.
• To remove all the conditional formatting from any worksheet, we can use the ‘Delete’ method as follows:

Cells.FormatConditions.Delete

Recommended Articles

This has been a guide to VBA Conditional Formatting. Here we learn how to apply conditional formatting to an excel cell using the Format Conditions method in VBA along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

• VBA Wait Function
• VBA TimeValue
• Using Formulas with Conditional Formatting
• Conditional Formatting for Blank Cells