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" 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 WorksheetsNext: Increment invoice number