Prev: Automatic Update in Multiple Worksheets
Next: Increment invoice number
From: SNC-DW on 12 Oct 2009 07:35

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
From: Per Jessen on 12 Oct 2009 09:02
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" <SNC-DW.518db36(a)excelbanter.com> skrev i meddelelsen
news:SNC-DW.518db36(a)excelbanter.com...
>
> 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

From: Luke M on 12 Oct 2009 09:08
'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
>
From: SNC-DW on 13 Oct 2009 06:52

Thanks for your help guys, much appreciated!

Luke M;889524 Wrote:
> '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
> > -




--
SNC-DW
 | 
Pages: 1
Prev: Automatic Update in Multiple Worksheets
Next: Increment invoice number