VBA Conditional Formatting

Updated on January 1, 2024
Article byTwinkle Sethi
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Conditional Formatting in Excel VBA

We can apply conditional formattingApply Conditional FormattingConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more 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:

vba conditional formatting parameters

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Examples of VBA Conditional Formatting

Below are examples of conditional formatting in Excel VBA.

You can download this VBA Conditional Formatting Template here – VBA Conditional Formatting Template

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:

VBA Conditional Formatting Example 1

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

  • Go to Developer -> Visual Basic Editor:
VBA Conditional Formatting Example 1-1
  • Right-click on the workbook name in the ‘Project-VBAProject’ pane-> ‘Insert’-> ‘Module.’
VBA Conditional Formatting Example 1-2
  • Now write the code/procedure in this module:

Code:

Sub formatting()

End Sub
VBA Conditional Formatting Example 1-3
  • Define the variable rng, condition1, condition2:

Code:

Sub formatting()

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

End Sub
VBA Conditional Formatting Example 1-4

Code:

Sub formatting()

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

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

End Sub
VBA Conditional Formatting Example 1-5
  • 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
VBA Conditional Formatting Example 1-6
  • 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
VBA Conditional Formatting Example 1-7
  • Define and set the format to be applied for each condition.
Example 1-8

Copy and paste this code into your VBA class moduleVBA Class ModuleUsers have the ability to construct their own VBA Objects in VBA Class Modules. The objects created in this module can be used in any VBA project.read more.

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:

VBA Conditional Formatting 1-9

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

Font Object

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:

Example 2-1

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

Code:

Sub TextFormatting()

End Sub
Example 2-2

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
Example 2-3

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:

VBA Conditional Formatting Example 2-3

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.’

Things to Remember About VBA Conditional Formatting

  • 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

Recommended Articles

This article 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, practical examples, and a downloadable template. Below you can find some useful Excel VBA articles: –