percentage of green cells in list
 

Hi
i have a list of dates coloured either red or green in column A and i
want to express the green cells as a percentage of the total list ie
12/02/10
14/02/10
20/0210 etc
this list is constantly being added to

thanks for any
help

rslc

percentage of green cells in list
 

There is no built-in excel function to do this. But you can use a UDF that
looks at the range and returns the sum of color. But that function will not
recalculate if you change color. Every time you change the color you will
need to recalculate or wait excel to recalculate...

To install the UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.

Function COLORCOUNT(varRange As Range, varColor As Range)
Dim cell As Range
For Each cell In varRange
If cell.Interior.ColorIndex = varColor.Interior.ColorIndex Then
ColorCount = ColorCount + 1
End If
Next
End Function

ITo use the as a formula in range A1:A10 try the below; which will count the
number of blue cells....The second argument denotes the colored cell (to be
queried).

=COLORCOUNT(A1:A10,B1)
--
Jacob


"rslc" wrote:

Hi
i have a list of dates coloured either red or green in column A and i
want to express the green cells as a percentage of the total list ie
12/02/10
14/02/10
20/0210 etc
this list is constantly being added to

thanks for any
help

rslc

.

percentage of green cells in list
 

Forgot to mention that the below do not work for Conditional Formatted cells.

--
Jacob


"Jacob Skaria" wrote:

There is no built-in excel function to do this. But you can use a UDF that
looks at the range and returns the sum of color. But that function will not
recalculate if you change color. Every time you change the color you will
need to recalculate or wait excel to recalculate...

To install the UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.

Function COLORCOUNT(varRange As Range, varColor As Range)
Dim cell As Range
For Each cell In varRange
If cell.Interior.ColorIndex = varColor.Interior.ColorIndex Then
ColorCount = ColorCount + 1
End If
Next
End Function

ITo use the as a formula in range A1:A10 try the below; which will count the
number of blue cells....The second argument denotes the colored cell (to be
queried).

=COLORCOUNT(A1:A10,B1)
--
Jacob


"rslc" wrote:

Hi
i have a list of dates coloured either red or green in column A and i
want to express the green cells as a percentage of the total list ie
12/02/10
14/02/10
20/0210 etc
this list is constantly being added to

thanks for any
help

rslc

.

percentage of green cells in list
 

Thanks Jacob
unfortunately my list is conditionally formatted and
i was hoping to use a formula as a conditional format to achieve this

but thanks for the help


rslc

"Jacob Skaria" wrote in message
...
Forgot to mention that the below do not work for Conditional Formatted
cells.

--
Jacob


"Jacob Skaria" wrote:

There is no built-in excel function to do this. But you can use a UDF
that
looks at the range and returns the sum of color. But that function will
not
recalculate if you change color. Every time you change the color you
will
need to recalculate or wait excel to recalculate...

To install the UDF (User Defined function). From workbook launch VBE
using
Alt+F11. From menu Insert a Module and paste the below function.Close and
get
back to workbook and try the below formula.

Function COLORCOUNT(varRange As Range, varColor As Range)
Dim cell As Range
For Each cell In varRange
If cell.Interior.ColorIndex = varColor.Interior.ColorIndex Then
ColorCount = ColorCount + 1
End If
Next
End Function

ITo use the as a formula in range A1:A10 try the below; which will count
the
number of blue cells....The second argument denotes the colored cell (to
be
queried).

=COLORCOUNT(A1:A10,B1)
--
Jacob


"rslc" wrote:

Hi
i have a list of dates coloured either red or green in column A
and i
want to express the green cells as a percentage of the total list ie
12/02/10
14/02/10
20/0210 etc
this list is constantly being added to

thanks for
any
help

rslc

.