Well if that's what the Customer wants then so be it. Thanks for the feedback.

Mike

"Kelvin" wrote:

Thanks Mike - your answer's nailed it, as ever!

Incidentally, the idea here is iterate thru a range of results that are
coloured red/amber/green for various units, but if a result is n/a for a unit
then the cell's coloured black/not colored and the total is adjusted
downwards accordingly for that unit. Which is what they want..!

Regards,

KC

"Mike H" wrote:

Hi,

I'm not sure what your trying to do here because this returns either -3 or
+3 for each cell in the range but it now will iterate through all cells in
your range.

Function ReturnPercentage(InRange As Range) As Integer
'Iterates thru cells in inRange, providing possible max total of cells
'unless cell has BackColor of Black or is blank,
'in which case 3 is deducted from poss max total
Application.Volatile True
Dim PossMax As Integer
Dim tmp
For Each c In InRange
tmp = c.Interior.ColorIndex
Select Case tmp
Case 56, -4142 'Black or null/white
PossMax = PossMax - 3
Case Else
PossMax = PossMax + 3
End Select
Next c
ReturnPercentage = PossMax
End Function

Mike

"Kelvin" wrote:

Hi all

Probably a basic one, but I've been away from this for a bit & I've
forgotten how to do this...

Trying to write a function that iterates thru the cells in a range, and
comes to a total depending on the BackColor of each cell.

Calling it like this, which results in #Value! -
=ReturnPercentage(E8:AB8)

Function ReturnPercentage(InRange As Range) As Integer
'Iterates thru cells in inRange, providing possible max total of cells
unless cell has BackColor of Black or is blank,
'in which case 3 is deducted from poss max total

Application.Volatile True

Dim PossMax As Integer

Dim tmp

For Each cell In ActiveSheet.Range(InRange)
tmp = InRange(1, 1).Interior.ColorIndex

Select Case tmp
Case 56, -4142 'Black or null/white
PossMax = PossMax - 3
Case Else
PossMax = PossMax + 3
End Select
Next cell

ReturnPercentage = PossMax

End Function

... All/any help gratefully received.

Thanks for looking

Kelvin