To conditionally formatting above or below average values in a range use the AddAboveAverage method of the FormatConditions object. With the AboveBelow property specify what to format – constants shown in the table below.
| Name | Value | Description |
|---|---|---|
| XlAboveAverage | 0 | Above average. |
| XlAboveStdDev | 4 | Above standard deviation. |
| XlBelowAverage | 1 | Below average. |
| XlBelowStdDev | 5 | Below standard deviation. |
| XlEqualAboveAverage | 2 | Equal above average. |
| XlEqualBelowAverage | 3 | Equal below average. |
Sub abovebelowaverage()
Dim rg As Range
Dim av As AboveAverage
Set rg = Range("Q4", Range("Q4").End(xlDown))
Set av = rg.FormatConditions.AddAboveAverage
With av
.AboveBelow = xlEqualAboveAverage
.Interior.Color = vbYellow
End With
End Sub
The code above relates to the data shown below.
