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?

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.

. 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

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.

https://www.askmehelpdesk.com/other-programming/how-could-put-2-conditions-count-if-function-nested-if-excel-model-74793.html#post335426
(any one could help me with this.. I would be thankfull)