Iterate Thru Cells

[Kelvin]

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

 

[Mike H]

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

 

[Rick Rothstein \(MVP - VB\)]

Not sure what the +3/-3 is all about, but given what you posted, I think
this will do what you want...

Function ReturnPercentage(InRange As Range) As Integer
Dim Cell As Range
Application.Volatile True
For Each Cell In InRange
Select Case Cell.Interior.ColorIndex
Case 56, -4142 'Black or null/white
ReturnPercentage = ReturnPercentage - 3
Case Else
ReturnPercentage = ReturnPercentage + 3
End Select
Next
End Function

Rick

 

[Kelvin]

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]

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

Mike