Count IF Cell contains text AND cell colour is ....

[ktoon]

Hi,

I am trying to count data from Table 1, in Table 2 - but I need to only CountIF the cell contains the specific text (FRED,GEORGE etc.) AND a specific colour as shown.

Table 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]*RED*[/TD]
[TD]*GREEN*[/TD]
[/TR]
[TR]
[TD]1:00pm[/TD]
[TD]FRED[/TD]
[TD]GEORGE[/TD]
[/TR]
[TR]
[TD]2:00pm[/TD]
[TD]DAVE[/TD]
[TD]DAVE[/TD]
[/TR]
</tbody>[/TABLE]


Table 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]*RED*[/TD]
[TD]*GREEN*[/TD]
[/TR]
[TR]
[TD]FRED[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]GEORGE[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]DAVE[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


At the moment I have am using this VBA:

Function CountCcolor(range_data As Range, criteria As Range) As Long    Dim datax As Range
    Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
        CountCcolor = CountCcolor + 1
    End If
Next datax
End Function

With this formula:

=CountCcolor(DeliveryLog!$B$3:$F$17,Data!$B$1)

But I have not idea how to use this with COUNTIF(S) or anything because you have to choose range and criteria separately.

Any ideas anyone?

(Excel 2010)

Thanks
Kyle