[eddiegeorge]

In November 2004 Leif 5233 wrote the following regarding counting cells highlighted in a certain colour:

------------------
Create this function:

Function CountCol(SumRange As Range, intColor As Integer) As Integer
Dim I As Integer
Dim Cell As Range

Set SumRange = SumRange.SpecialCells(xlCellTypeAllFormatCondition s)

For Each Cell In SumRange
If Cell.Interior.ColorIndex = intColor Then
I = I + 1
End If

Next Cell

CountCol = I

End Function

Then in the cell where you would like the total, put the following formula:

=CountCol(a1:a100,6)

That formula will show you the total count of all cells with colorindex of 6 (Yellow) in range a1:a100. You can fix it up to use color names if you like, but this works fine for me. There is a listing of basic colorindexes in Excel's help.

Hope this helps,
Leif
-----------------

This works great unless the cell is highlighted as a result of conditional formating. Is there any way of counting cells that are highlighted due to conditional formating?

[ScottGem]

Instead of checking the format of the cell, check the condition. You can modify the line:

If Cell.Interior.ColorIndex = intColor Then

To test for the condition rather than the colorindex.

[ahmed kamal]

. I have 2 sets of data (x and y), I want to count (less than and more than) above each data (for x and y) and also (less than and more than) below the data itself (for x and y). This is for each data x and y. What is the proper function I can use. Many thanks

[ScottGem]

Not sure I follow. If you want to compare X and Y and count how many times x > y and y > x, then I would add a column with an expression like:

=IF(A2>B2,"Over","Under")

This assumes that x is in the A column and Y in the B Column. The result will put the text Over and Under for each row. You can then use CountIf to count each.

[miss-java]

https://www.askmehelpdesk.com/other-...tml#post335426
(any one could help me with this.. I would be thankfull)