filling range with colour

[vssa]

Hi, im pretty new to vba and im trying to write a macro that will allow me to
1. get the mean average of numbers in a range
2. then colour fill the range depending on what the mean average is

so far ive managed to write in out in principal i was wandering if any one could guide me in the right direction ?

cheers

Sub mean_average_color()


color range b5:t5 red


if average of b8:t8 is < (1.5 * average b5:t5)
color b8:y8 red

if average of b8:t8 is > (1.5 * average b5:t5)
color b8:y8 blue

if average of b8:t8 is > (2.2 * average b5:t5)
color b8:y8 green



if average of b11:t11 is < (1.5 * average b5:t5)
color b11:y11 red

if average of b11:t11 is > (1.5 * average b5:t5)
color b11:y11 blue

if average of b11:t11 is > (2.2 * average b5:t5)
color b11:y11 green



if average of b14:t14 is < (1.5 * average b5:t5)
color b14:y14 red

if average of b14:t14 is > (1.5 * average b5:t5)
color b14:y14 blue

if average of b14:t14 is > (2.2 * average b5:t5)
color b14:y14 green




if average of b17:t17 is < (1.5 * average b5:t5)
color b17:y17 red

if average of b17:t17 is > (1.5 * average b5:t5)
color b17:y17 blue

if average of b17:t17 is > (2.2 * average b5:t5)
color b17:y17 green

End sub

 

[Snakehips]

vssa,

Here is some code that should help you on your way.
It will run as is but I leave it for you to complete.......

Sub mean_average_color()
On Error Resume Next
Range("B5:T5").Cells.Interior.ColorIndex = 3  'Red
Av1 = Application.WorksheetFunction.Average(Range("B5:T5"))
Av2 = Application.WorksheetFunction.Average(Range("B8:T8"))
If Av2 < 1.5 * Av1 Then Range("B8:Y8").Cells.Interior.ColorIndex = 3  'Red
If Av2 > 1.5 * Av1 Then Range("B8:Y8").Cells.Interior.ColorIndex = 4   'Green
If Av2 > 2.2 * Av1 Then Range("B8:Y8").Cells.Interior.ColorIndex = 5   'Blue
 
'*******
Av3 = Application.WorksheetFunction.Average(Range("B11:T11"))
If Av3 < 1.5 * Av1 Then Range("B11:Y11").Cells.Interior.ColorIndex = 3  'Red
If Av3 > 1.5 * Av1 Then Range("B11:Y11").Cells.Interior.ColorIndex = 4   'Green
If Av3 > 2.2 * Av1 Then Range("B11:Y11").Cells.Interior.ColorIndex = 5   'Blue
 
'*******
'etc etc
 
 
'if average of b14:t14 is < (1.5 * average b5:t5)
'Color b14: y14 Red
'if average of b14:t14 is > (1.5 * average b5:t5)
'Color b14: y14 blue
'if average of b14:t14 is > (2.2 * average b5:t5)
'Color b14: y14 green
 
 
'if average of b17:t17 is < (1.5 * average b5:t5)
'Color b17: y17 Red
'if average of b17:t17 is > (1.5 * average b5:t5)
'Color b17: y17 blue
'if average of b17:t17 is > (2.2 * average b5:t5)
'Color b17: y17 green
On Error GoTo 0
End Sub

Hope that helps.

 

[vssa]

@ snakehips

really big help, thanks ever so much