if function

New Contributor

I am trying to create an if statement where if a cell is a certain colour, I would like the average of all of those cells of that colour. 


I have created a module for interior colour - 

Function InteriorColor(CellColor As Range)
InteriorColor = CellColor.Interior.ColorIndex
End Function


Then in a cell have =interiorcolor(U5)

What is the next formula I should use so that if any cells have this colour it will average them?



2 Replies
best response confirmed by allyreckerman (Microsoft)


Let's say that the range with the numbers and colors is T5:T100, and that the InteriorColor formulas are in U5:U100.

The average value of the cells in T5:T100 with the same color as T5 is



@Hans Vogelaar Awesome thanks - took a few times reading what you wrote to work it out but I've got it now. Thanks for your help