Hi

I've used the following code to count the number of cells in a range that are filled yellow, using the basic funtion '=CountYellow(A1:A99)'

Function CountYellow(MyRange As Range)
Dim iCount As Integer
Application.Volatile
iCount = 0
For Each cell In MyRange
If cell.Interior.ColorIndex = 6 Then
iCount = iCount + 1
End If
Next cell
CountYellow = iCount
End Function

However I now need to count the same range of cell that are coloured but contain only zeros, any ideas?

Thanks

Hi

You can add some conditions to the if statement:

If cell.Interior.ColorIndex = 6 And cell.Value < "" And cell.Value = 0 Then

If you use the below it will count empty cells as 0:

If cell.Interior.ColorIndex = 6 And cell.Value = 0 Then

Regards,
Per

"SNC-DW" skrev i meddelelsen
...

Hi

I've used the following code to count the number of cells in a range
that are filled yellow, using the basic funtion '=CountYellow(A1:A99)'

Function CountYellow(MyRange As Range)
Dim iCount As Integer
Application.Volatile
iCount = 0
For Each cell In MyRange
If cell.Interior.ColorIndex = 6 Then
iCount = iCount + 1
End If
Next cell
CountYellow = iCount
End Function

However I now need to count the same range of cell that are coloured
but contain only zeros, any ideas?

Thanks




--
SNC-DW

'Changed function name for clarity

Function CountYellowAndZero(MyRange As Range)
Dim iCount As Integer
Application.Volatile
iCount = 0
For Each cell In MyRange
'added criteria
If cell.Interior.ColorIndex = 6 And _
cell.Value = 0 And cell.Value < "" Then
iCount = iCount + 1
End If
Next cell
CountYellow = iCount
End Function

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"SNC-DW" wrote:


Hi

I've used the following code to count the number of cells in a range
that are filled yellow, using the basic funtion '=CountYellow(A1:A99)'

Function CountYellow(MyRange As Range)
Dim iCount As Integer
Application.Volatile
iCount = 0
For Each cell In MyRange
If cell.Interior.ColorIndex = 6 Then
iCount = iCount + 1
End If
Next cell
CountYellow = iCount
End Function

However I now need to count the same range of cell that are coloured
but contain only zeros, any ideas?

Thanks




--
SNC-DW

Thanks for your help guys, much appreciated!