Set condition1 = (xlCellValue, xlGreater, '=80')

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

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

Set/fix the range on which conditional formatting is to be desired using the VBA ‘Range’ function:.

Define the variable rng, condition1, condition2:ĭim condition1 As FormatCondition, condition2 As FormatCondition.

Now write the code/procedure in this module:.

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

Go to Developer -> Visual Basic Editor:.

We use the FormatConditions.Add the function as below to accomplish this: Let us see the data contained in the file: 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. You can download this VBA Conditional Formatting Template here – VBA Conditional Formatting Template Example #1

Formula2: Optional, represents the value or expression associated with the second part of conditional format when the parameter: ‘Operator’ is either ‘xlBetween’ or ‘xlNotBetween.’įormatConditions.Modify also has the same syntax as FormatConditions.Add.

Formula1: Optional, represents the value or expression associated with the conditional format.

Operator: Optional, represents the operator to be used with a value when ‘Type’ is based on cell value.

Type: Required, represents if the conditional format is based on value present in the cell or an expression.

The Add formula syntax has the following arguments: FormatConditions is a property of the Range object, and Add the following parameters with below syntax: FormatConditions.Add (Type, Operator, Formula1, Formula2) Each format is represented by a FormatCondition object. It contains all conditional formats for a single range and can hold only three format conditions.įormatConditions.Add/Modify/Delete is used in VBA to add/modify/delete FormatCondition objects to the collection. This conditional formatting can also be done in excel VBA programming using the ‘ Format Conditions Collection’ in the macro/procedure.įormat Condition is used to represent a conditional format that can be set by calling a method that returns a variable of that type. 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. read more to a cell or range of cells in Excel.

It can be found in the styles section of the Home tab. We can apply conditional formatting Apply Conditional Formatting Conditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions.

Conditional formatting excel youtube