Tim,
I think that you need to do a test on the same condition that you used in
the conditional format. So for instance, if they are shaded red if they are
less than 500, use the formula provided. In other words, replicate your
conditional formatting formula.
Or ....
You could try this technique that evolved out of previous threads between
Harlan Grove and I. Create a function to get the colorindex of cells, namely
Function ColorIndex(rng As Range) As Variant
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryColours As Variant
If rng.Areas.Count > 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If
If rng.Cells.Count = 1 Then
aryColours = rng.Interior.ColorIndex
Else
aryColours = rng.Value
i = 0
For Each row In rng.Rows
i = i + 1
j = 0
For Each cell In row.Cells
j = j + 1
aryColours(i, j) = cell.Interior.ColorIndex
Next cell
Next row
End If
ColorIndex = aryColours
End Function
Put this in a normal code module. To use it, either add a helper column, say
in B1, put
=ColorIndex(A1)
and copy down column B. Then in C1, add
=COUNTIF(B1:B100,10) where the 10 is the colour you wish to test, or
=COUNTIF(B1:B100,ColorIndex(C2))
where C2 is given the colour you want to check
Or if you want to do it in one fell swoop
=SUMPRODUCT(--(colorindex(A1:A100)=10))
or if you want to test directly the colourised cell
=SUMPRODUCT(--(colorindex(A1:A100)=colorindex(C2)))
One major problem you should b e aware of is that if you change a colour in
the target range, the formula does not recalculate as the interior colour
doesn't trigger calculation, so you have to force it by editing the cell.
