chosing cells with a specific colour and taking average value from

[Ksenija]

Hi,

I have cells with different colours. I want to go through a column and look
for the max.value of red cells, the average of red cells and the median of
red cells. S

So I want to ignore cells with other colours and just use cells with a
specific colour.

Can somebody help me with this? I'm very new at this, have tried for a while
now, but it's not working..

 

[Bob Phillips]

This should give you what you need

http://www.xldynamic.com/source/xld.ColourCounter.html

 

[Mike H]

Hi,

This assumes column A. Right click your sheet tab, view code and paste this
in and run it

Sub sonic()
Dim MyRange, myrange1 As Range, c As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
If c.Interior.ColorIndex = 3 Then
If myrange1 Is Nothing Then
Set myrange1 = c
Else
Set myrange1 = Union(myrange1, c)
End If
End If
redaverage = WorksheetFunction.Average(myrange1)
maxvalue = WorksheetFunction.Max(myrange1)
medianvalue = WorksheetFunction.Median(myrange1)
Next
End Sub


Mike

 

[Ksenija]

Hi,

I'm getting a error message when I am trying to do this. It says something
like "Error nr 5, nonvalid procedurecall or argument"..

what shall I do?

"Mike H" skrev: