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/