return the highest cell value in a range based on fill colour

[chris peach]

hi,

im trying to return the highest cell value in a range based on fill colour.

its a darts team sheet and for every outshot the cell is filled yellow with the value of the shot as a number,obviously there can be different values eg. 32 or 40 and i want to take the largest of these values into another cell automatically

the range would be from c3:w3 and c11:w11 and the highest value would be placed in cell j21

any help with this would be much appreciated.

cheers in advance

chris

 

[nikik]

I think I know what you need, but I may be reading it wrong.

You can change the "sort on" criteria in the sort window and then use a =MAX formula, selecting your sorted colors for the formula.

Let me know if that doesn't help.

 

[chris peach]

hi, thanks for quick reply, unfortunatley i dont want to sort the data as the spreadsheet is fixed, also there are other values in the whole range which i want to ignore.

ie a player could have say 3 shots in 1 game

shot 1 - 180
shot 2 - 180
shot 3 - 141

(shot 3 being the "out shot" and the "double shot", the cell containing the value of shot 3 gets filled manually yellow)

then have 5 shots in the next game with a "outshot" of 32

having 2 outshots i want to take the highest one and place that value into another cell.

the reason the cell is turned yellow as im using it as a way of counting the number of cells that are that colour.

sorry if it doesnt make it any clearer

cheers

chris

 

[nikik]

how are you counting the yellow ones? manually?

 

[chris peach]

i found a bit of vba to count the cells:

Function CountCol(SumRange As Range, intColor As Integer) As Integer
Dim i As Integer
Dim Cell As Range
Set SumRange = SumRange.SpecialCells(xlCellTypeAllFormatConditions)
For Each Cell In SumRange
If Cell.Interior.ColorIndex = intColor Then
i = i + 1
End If
Next Cell
CountCol = i
End Function