Counting coloured cells
 

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

Counting coloured cells
 

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

Counting coloured cells
 

'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!