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..

This should give you what you need

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

--
__________________________________
HTH

Bob

"Ksenija" wrote in message
...
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..

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" wrote:

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..

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:

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" wrote:

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..