Calculate the avg and color the cells when their values are above or below the avg

[Qomplainerz]

Example:

Sub CalculateAverageAndColorCode()
    Dim dataRange As Range
    Dim avgValue As Double
    Dim cell As Range

    'Set the range where you want to calculate the average and apply color coding
    Set dataRange = Range("A1:A10")

    'Calculate the average value of the range
    avgValue = Application.WorksheetFunction.Average(dataRange)

    'Color code cells based on whether their value is above or below the average
    For Each cell In dataRange
        If cell.Value > avgValue Then
            cell.Interior.Color = RGB(0, 255, 0) 'Green color for above average
        ElseIf cell.Value < avgValue Then
            cell.Interior.Color = RGB(255, 0, 0) 'Red color for below average
        End If
    Next cell
End Sub

Explanation:

The code defines a subroutine named CalculateAverageAndColorCode.
It declares variables for the data range (dataRange), the average value (avgValue), and a loop variable (cell) to iterate through each cell in the range.
Set dataRange = Range("A1:A10") specifies the range A1:A10 where you want to calculate the average and apply color coding.
The Application.WorksheetFunction.Average function is used to calculate the average value of the dataRange.
The For Each loop is used to iterate through each cell in dataRange.
If a cell's value is above the average, it is colored green using cell.Interior.Color = RGB(0, 255, 0).
If a cell's value is below the average, it is colored red using cell.Interior.Color = RGB(255, 0, 0).