Format Condition Object

You can add conditional formatting to your spreadsheet by using the For-matCondition object. Conditional formatting is associated with a particular range of cells. The FormatCondition object is a member of the Format-Conditions collection. This collection can contain up to three FormatCondition objects for a given range. Use the Count method of the FormatConditions collection to return the number of objects in the collection. Use the Add method of the FormatConditions collection to create a new conditional format. This method requires that you specify the type constant (xlCellValue, xlExpression) to indicate whether the conditional format is based on a cell value or an expression. The Add method also has three optional arguments (Operator, Formula1, and Formula2) that allow you to specify the condition. Use the Modify method to modify the formatting condition. Use the Delete method to delete a formatting condition. The example procedure below creates a conditional format to be applied to all non-numeric cells in the active sheet when the cell value is greater than or equal to 150. Notice how the cell with a value of 150 is formatted with white font color and colored background.

Citricidal

Liquid

10 onz

Vitamin B12

Tablets

50

Licorice Root

Tablets

100

Ginger Root

Tablets

150

Sub ApplyConditionalFormat()

Dim objFormatCon As FormatCondition Dim objFormatColl As FormatConditions Dim myRange As Range

Sub ApplyConditionalFormat()

Dim objFormatCon As FormatCondition Dim objFormatColl As FormatConditions Dim myRange As Range

' select range containing numeric cells only Set myRange = ActiveSheet.UsedRange. _

SpecialCells(xlCellTypeConstants, 1) Set objFormatColl = myRange.FormatConditions

' find out if any conditional formatting already exists If objFormatColl.Count > 0 Then

MsgBox "There are " & objFormatColl.Count & " conditions " & "defined for the used range."

End If remove existing conditions if they exist

For Each objFormatCon In objFormatColl objFormatCon.Delete

Next

' add first condition

Set objFormatCon = objFormatColl.Add(Type:=xlCellValue, _ Operator:=xlGreaterEqual, _ Formula1:="150") With objFormatCon .Font.Bold = True .Font.Colorlndex = 2 ' white .Interior.Pattern = xlSolid .Interior.Color = RGB(0, 0, 255) ' blue End With

End Sub

0 0

Post a comment