Thread: Counting colours from conditional formatting

1. Counting colours from conditional formatting

Hi All,

I've tried a variety of options to count colours that worked when the colour is not generated by conditional formatting, but can't get it to work when conditional formatting is applied.

In the attached example you can see where I'm trying to do the count (starting at cell O9 & P9 and down)

Any suggestions would be appreciated.

Regards

2. In O9:
=SUM(COUNTIF(E9:N9,\$E\$3:\$N\$3))
entered with Ctrl+Shift+Enter
In P9:
=COUNT(E9:N9)-O9
and fill down.

3. The Following User Says Thank You to rory For This Useful Post:

4. Thanks Rory,

That works

Just as a matter of interest, is there a way to count colours for conditionally formatted cells?

Regards

You can use a line like this

For Each cell In rng
If Evaluate(cell.FormatConditions(1).Formula1) = True Then

6. Thanks Maudibe,

I'm still trying to learn VBA - how would this look as code?

Regards

7. Maybe something like this (not tested)

Code:
```Dim rng As Range, cell As Range
For I = 9 To 26
Set rng = Range(Cells(I, "E"), Cells(I, "N"))
For Each cell In rng
If Evaluate(cell.FormatConditions(1).Formula1) = True Then
Count = Count + 1
End If
Next cell
Cells(I, "P") = Count
Cells(I, "O") = 10 - Count
Count = 0
Next I```
For example, If condition 1 turns the cell green, you can count then number of incidents of True for that condition in a row which should be the same as the number of conditionally green colored cells.

HTH,
Maud

8. The Following User Says Thank You to Maudibe For This Useful Post:

9. For basic colour CF it can be done but is not that simple - see Chip's page here: http://www.cpearson.com/Excel/CFColors.htm

If you have 2010 or later there is a much better option - the DisplayFormat property - but you can't call it from a function in a cell - you'd have to use a subroutine eg an event.

10. The Following User Says Thank You to rory For This Useful Post:

11. An alternative array formula that would also work in cell O9 is:

{=COUNT(MATCH(E9:N9,\$E\$3:\$N\$3,0))}

then copy down.