AboveAverage object (Excel)

Represents an above average visual of a conditional formatting rule. Applies a color or fill to a range or selection to help you see the value of a cell relative to other cells.


All conditional formatting objects are contained within a FormatConditions collection object, which is a child of a Range collection.

You can create an above average formatting rule by using either the Add or AddAboveAverage method of the FormatConditions collection.


The following example builds a dynamic data set and applies color to the above average values through conditional formatting rules.

Sub AboveAverageCF() 
' Building data for Melanie 
 Range("A1").Value = "Name" 
 Range("B1").Value = "Number" 
 Range("A2").Value = "Melanie-1" 
 Range("A2").AutoFill Destination:=Range("A2:A26"), Type:=xlFillDefault 
 Range("B2:B26").FormulaArray = "=INT(RAND()*101)" 
' Applying Conditional Formatting to items above the average. Should appear green fill and dark green font. 
 Selection.FormatConditions(1).AboveBelow = xlAboveAverage 
 With Selection.FormatConditions(1).Font 
 .Color = -16752384 
 .TintAndShade = 0 
 End With 
 With Selection.FormatConditions(1).Interior 
 .PatternColorIndex = xlAutomatic 
 .Color = 13561798 
 .TintAndShade = 0 
 End With 
MsgBox "Added an Above Average Conditional Format to Melanie's data. Press F9 to update values.", vbInformation 
End Sub



See also

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.