I'm trying to prepare a code in vba for excel. For each month there is a list of values, each month is listed in a separate column, multiple values are listed below each month.
What I wanted to do is change the color of each cell which value is 4 times smaller than the month's average. So far my code looks like this, each average is calculated like I wanted it, but i can't seem to figure out how to get it to compare the cells only to one column's average, not to all of them consecutively - like it does now.
Sub MonthAverage()
Dim Data As Variant
Set Data = Selection
Dim rows As Integer
Dim columns As Integer
rows = Data.rows.Count
columns = Data.columns.Count
Dim average As Double
For j = 1 To columns
average = 0#
For i = 1 To rows
average = average + Data.Cells(i, j)
Next
average = average / rows
For Each cell In Data.Cells
If cell.Value <= average / 4 Then cell.Interior.Color = RGB(0, 0, 255)
Next
MsgBox average 'just to make sure I got the averages correctly
Next
End Sub
Bookmarks