I am trying to do a Lotto 649 sheet which marks each number you get b
filling in the block with a fill color, and also at the end of the ro
give a count how many numbers you have.
I have tried to do this but I can only get one or the other to work.
I can't get the two formulas to work together on one sheet
Below are the formulas

Formula 1 - =CountColor($A$2,B3:K3)

Code
Function CountColor(rColor As Range, rSumRange As Range)

''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com

'Counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
Dim rCell As Range
Dim iCol As Integer
Dim vResult

iCol = rColor.Interior.ColorIndex

For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell

CountColor = vResult
End Function

Formula 2 -
Choose Format|Conditional Formatting
From the first dropdown, choose Formula Is
For the formula, use the CountIf function:
=COUNTIF($A$45:$K$49,B3)
Click the Format button.
Select formatting options (green pattern,), click OK
Click OK

Thanks Keit

--
Message posted from http://www.ExcelForum.com

Hi
unfortunately there is nothing you can do. Colors created by a
conditional format can not be evaluated in VBA. That is if you check
the colorindex in VBA always the default color index is returned.

--
Regards
Frank Kabel
Frankfurt, Germany

I am trying to do a Lotto 649 sheet which marks each number you get
by
filling in the block with a fill color, and also at the end of the
row
give a count how many numbers you have.
I have tried to do this but I can only get one or the other to work.
I can't get the two formulas to work together on one sheet
Below are the formulas

Formula 1 - =CountColor($A$2,B3:K3)

Code
Function CountColor(rColor As Range, rSumRange As Range)

''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com

'Counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
Dim rCell As Range
Dim iCol As Integer
Dim vResult

iCol = rColor.Interior.ColorIndex

For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell

CountColor = vResult
End Function

Formula 2 -
Choose Format|Conditional Formatting
From the first dropdown, choose Formula Is
For the formula, use the CountIf function:
=COUNTIF($A$45:$K$49,B3)
Click the Format button.
Select formatting options (green pattern,), click OK
Click OK

Thanks Keith


---
Message posted from http://www.ExcelForum.com/

"Frank Kabel" wrote...
unfortunately there is nothing you can do. Colors created by a
conditional format can not be evaluated in VBA. That is if you check
the colorindex in VBA always the default color index is returned.

Wrong. While cells' color properties don't reflect colors set by conditional
formatting, those cells have FormatConditions collection object properties,
and those *CAN* be evaluated in VBA. The only trick is catching relative
cell refs in formula conditions, replacing refs to Application.Caller with
refs to cells in the proper range. This isn't fool-proof, but it handles
most likely situations.


Function ugh(rng As Range, ci As Variant) As Long
Dim f As FormatCondition, x As String, r As Range, a As String

a = Application.Caller.Address(0, 0)

For Each r In rng
For Each f In r.FormatConditions
If f.Interior.ColorIndex = ci Then

If f.Type = xlExpression Then
x = Application.WorksheetFunction.Substitute(f.Formula 1, _
a, r.Address(0, 0))
If Evaluate(x) Then ugh = ugh + 1

ElseIf (f.Operator = xlEqual And r.Value = f.Formula1) _
Or (f.Operator = xlNotEqual And r.Value < f.Formula1) _
Or (f.Operator = xlLess And r.Value < f.Formula1) _
Or (f.Operator = xlLessEqual And r.Value <= f.Formula1) _
Or (f.Operator = xlGreater And r.Value f.Formula1) _
Or (f.Operator = xlGreaterEqual And r.Value = f.Formula1) Then
ugh = ugh + 1

ElseIf (f.Operator = xlBetween And f.Formula1 <= r.Value _
And r.Value <= f.Formula2) Or (f.Operator = xlNotBetween _
And (r.Value < f.Formula1 Or f.Formula2 < r.Value)) Then
ugh = ugh + 1

End If

End If
Next f
Next r

End Function

Hi Harlan
o.k. I'm convinced :-)
Nice UDF by the way

Frank

Harlan,

This is similar to a routine I have for getting CF colorindex (obvious I
suppose, there is only one way really), but I was interested to check if you
had solved the problem that I have with the colorindexs when a CF expression
is used. Interestingly, you have improved upon it, and it seems to be the
adjustment of the formula from the caller's address. This is neat, it really
seems to improve my routine - lots more checking to do yet, but it looks
promising, so I thank you in advance.

Still seems to be a problem if the formula has no anchoring cell reference
(such as =MOD(ROW(),3)=1), but in many instances this can be circumvented by
better formula definition (=MOD(ROW(A1),3)=1). I am sure that there are
still problems out there, after all why it doesn't work by just checking the
colorindex suggests something wrong in the engine room, but it's a good
step forward.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Harlan Grove" wrote in message
...
"Frank Kabel" wrote...
unfortunately there is nothing you can do. Colors created by a
conditional format can not be evaluated in VBA. That is if you check
the colorindex in VBA always the default color index is returned.

Wrong. While cells' color properties don't reflect colors set by
conditional
formatting, those cells have FormatConditions collection object
properties,
and those *CAN* be evaluated in VBA. The only trick is catching relative
cell refs in formula conditions, replacing refs to Application.Caller with
refs to cells in the proper range. This isn't fool-proof, but it handles
most likely situations.


Function ugh(rng As Range, ci As Variant) As Long
Dim f As FormatCondition, x As String, r As Range, a As String

a = Application.Caller.Address(0, 0)

For Each r In rng
For Each f In r.FormatConditions
If f.Interior.ColorIndex = ci Then

If f.Type = xlExpression Then
x = Application.WorksheetFunction.Substitute(f.Formula 1, _
a, r.Address(0, 0))
If Evaluate(x) Then ugh = ugh + 1

ElseIf (f.Operator = xlEqual And r.Value = f.Formula1) _
Or (f.Operator = xlNotEqual And r.Value < f.Formula1) _
Or (f.Operator = xlLess And r.Value < f.Formula1) _
Or (f.Operator = xlLessEqual And r.Value <= f.Formula1) _
Or (f.Operator = xlGreater And r.Value f.Formula1) _
Or (f.Operator = xlGreaterEqual And r.Value = f.Formula1) Then
ugh = ugh + 1

ElseIf (f.Operator = xlBetween And f.Formula1 <= r.Value _
And r.Value <= f.Formula2) Or (f.Operator = xlNotBetween _
And (r.Value < f.Formula1 Or f.Formula2 < r.Value)) Then
ugh = ugh + 1

End If

End If
Next f
Next r

End Function

"Bob Phillips" wrote...
....
Still seems to be a problem if the formula has no anchoring cell
reference
(such as =MOD(ROW(),3)=1), but in many instances this can be
circumvented by
better formula definition (=MOD(ROW(A1),3)=1). I am sure that there
are
still problems out there, after all why it doesn't work by just
checking the
colorindex suggests something wrong in the engine room, but it's a
good
step forward.
....

The simplest way to deal with either ROW() or COLUMN() would be to
replace them with ROW(r.Address(0, 0)) and COLUMN(r.Address(0, 0))
with r defined as in my udf.

Harlan,

Yeah good, but it does mean that you have to parse the formula to know that
it has no cell reference or not. But I suppose that it needs to be done to
make the routine more robust.

Bob

"Harlan Grove" wrote in message
om...
"Bob Phillips" wrote...
...
Still seems to be a problem if the formula has no anchoring cell
reference
(such as =MOD(ROW(),3)=1), but in many instances this can be
circumvented by
better formula definition (=MOD(ROW(A1),3)=1). I am sure that there
are
still problems out there, after all why it doesn't work by just
checking the
colorindex suggests something wrong in the engine room, but it's a
good
step forward.
...

The simplest way to deal with either ROW() or COLUMN() would be to
replace them with ROW(r.Address(0, 0)) and COLUMN(r.Address(0, 0))
with r defined as in my udf.

Frank is not absolutely correct. You can count CF colours that are set by a
condition, but it seems to fail when counting colours set by an expression
(such as your COUNTIF). Doesn't help you I am afraid, but just for info.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
unfortunately there is nothing you can do. Colors created by a
conditional format can not be evaluated in VBA. That is if you check
the colorindex in VBA always the default color index is returned.

--
Regards
Frank Kabel
Frankfurt, Germany

I am trying to do a Lotto 649 sheet which marks each number you get
by
filling in the block with a fill color, and also at the end of the
row
give a count how many numbers you have.
I have tried to do this but I can only get one or the other to work.
I can't get the two formulas to work together on one sheet
Below are the formulas

Formula 1 - =CountColor($A$2,B3:K3)

Code
Function CountColor(rColor As Range, rSumRange As Range)

''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com

'Counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
Dim rCell As Range
Dim iCol As Integer
Dim vResult

iCol = rColor.Interior.ColorIndex

For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell

CountColor = vResult
End Function

Formula 2 -
Choose Format|Conditional Formatting
From the first dropdown, choose Formula Is
For the formula, use the CountIf function:
=COUNTIF($A$45:$K$49,B3)
Click the Format button.
Select formatting options (green pattern,), click OK
Click OK

Thanks Keith


---
Message posted from http://www.ExcelForum.com/

You can also look at Chip Pearson's page on this topic:

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


--
Regards,
Tom Ogilvy


"keith44 " wrote in message
...
I am trying to do a Lotto 649 sheet which marks each number you get by
filling in the block with a fill color, and also at the end of the row
give a count how many numbers you have.
I have tried to do this but I can only get one or the other to work.
I can't get the two formulas to work together on one sheet
Below are the formulas

Formula 1 - =CountColor($A$2,B3:K3)

Code
Function CountColor(rColor As Range, rSumRange As Range)

''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com

'Counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
Dim rCell As Range
Dim iCol As Integer
Dim vResult

iCol = rColor.Interior.ColorIndex

For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell

CountColor = vResult
End Function

Formula 2 -
Choose Format|Conditional Formatting
From the first dropdown, choose Formula Is
For the formula, use the CountIf function:
=COUNTIF($A$45:$K$49,B3)
Click the Format button.
Select formatting options (green pattern,), click OK
Click OK

Thanks Keith


---
Message posted from http://www.ExcelForum.com/