+- QP School (https://qomplainerzschool.lima-city.de)
+-- Forum: Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=3)
+--- Forum: Excel VBA Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=48)
+--- Thread: Calculate the avg and color the cells when their values are above or below the avg (/showthread.php?tid=5261)
Calculate the avg and color the cells when their values are above or below the avg - Qomplainerz - 07-27-2023
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).