I have a grid that conditionally formats the background colour to Green,
Red or White depending on a criteria. I want to count the number of
times green appears in a row. I've tried modifying the CountByColor
Function but to no avail.

Is this possible?

TIA

Jon

Function CountByColor(InputRange As Range, ColorRange As Range) As Long
Dim cl As Range, TempCount As Long, ColorIndex As Integer
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempCount = 0
For Each cl In InputRange.Cells
If cl.Font.ColorIndex = ColorIndex Then TempCount = TempCount +
1
Next cl
Set cl = Nothing
CountByColor = TempCount
End Function

On Sat, 08 Nov 2003 14:14:52 -0500, Crash <[email deleted] wrote:

I have a grid that conditionally formats the background colour to Green,
Red or White depending on a criteria. I want to count the number of
times green appears in a row. I've tried modifying the CountByColor
Function but to no avail.

Is this possible?

TIA

Jon

Function CountByColor(InputRange As Range, ColorRange As Range) As Long
Dim cl As Range, TempCount As Long, ColorIndex As Integer
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempCount = 0
For Each cl In InputRange.Cells
If cl.Font.ColorIndex = ColorIndex Then TempCount = TempCount +
1
Next cl
Set cl = Nothing
CountByColor = TempCount
End Function

How about a Select Case that looks at the formula or the value? If it
can be expressed as an Excel formula, it can be expressed in VBA code.
--
auric "underscore" "underscore" "at" hotmail "dot" com
*****
Don't question authority, he doesn't know either.

Conditional formatting doesn't return a value you can check on directly.
See Chip Pearson's site for an explanation and code:

http://www.cpearson.com/excel/CFColors.htm

--
Regards,
Tom Ogilvy

Crash wrote in message
...
I have a grid that conditionally formats the background colour to Green,
Red or White depending on a criteria. I want to count the number of
times green appears in a row. I've tried modifying the CountByColor
Function but to no avail.

Is this possible?

TIA

Jon

Function CountByColor(InputRange As Range, ColorRange As Range) As Long
Dim cl As Range, TempCount As Long, ColorIndex As Integer
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempCount = 0
For Each cl In InputRange.Cells
If cl.Font.ColorIndex = ColorIndex Then TempCount = TempCount +
1
Next cl
Set cl = Nothing
CountByColor = TempCount
End Function

On Sat, 08 Nov 2003 14:14:52 -0500, Crash wrote:

I have a grid that conditionally formats the background colour to Green,
Red or White depending on a criteria. I want to count the number of
times green appears in a row. I've tried modifying the CountByColor
Function but to no avail.

Is this possible?

TIA

Jon

I do not believe so. You would need your code to check for the *condition*
that produces the Green result.


--ron

Thanks Tom, unfortunately the addresses are all relative so Chip's functions
didn't work for me, I ended-up writing a macro to loop through the rows
comparing each column to the master.

Jon

Tom Ogilvy wrote:

Conditional formatting doesn't return a value you can check on directly.
See Chip Pearson's site for an explanation and code:

http://www.cpearson.com/excel/CFColors.htm

--
Regards,
Tom Ogilvy

Crash wrote in message
...
I have a grid that conditionally formats the background colour to Green,
Red or White depending on a criteria. I want to count the number of
times green appears in a row. I've tried modifying the CountByColor
Function but to no avail.

Is this possible?

TIA

Jon

Function CountByColor(InputRange As Range, ColorRange As Range) As Long
Dim cl As Range, TempCount As Long, ColorIndex As Integer
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempCount = 0
For Each cl In InputRange.Cells
If cl.Font.ColorIndex = ColorIndex Then TempCount = TempCount +
1
Next cl
Set cl = Nothing
CountByColor = TempCount
End Function