COUNTIF depending on cell fill colour

[Guest]

Hi,

Is it posible and how, to count number of cells in a range that are a
perticular fill colour.

Thanks

 

[Guest]

See:

http://www.cpearson.com/excel/colors.htm

for a very good explanation

 

[Guest]

SORTED!

Thank Gary & Chip

See:

http://www.cpearson.com/excel/colors.htm

for a very good explanation

 

[Guest]

Thanks Gary,

Got it to work, but if the range's color is changed by conditional
formatting CountByColor does not work?, any ideas?

 

[Guest]

If the colors are painted by Conditional Formatting then the code can
actually ignore the color and focus on the condition itself.


Consider this simple example:

Let's say cells A1 thru A100 have been condtionally formatted to be bold if
the value is greater than 10. This allows us dis-regard the boldness and
instead:

Sub LaDdIe()
IAmTheCount = 0
For i = 1 To 100
If Cells(i, "A").Value > 10 Then
IAmTheCount = IAmTheCount + 1
End If
Next
MsgBox (IAmTheCount)
End Sub


Bob Phillips does a great job of reviewing this topic in:

http://www.xldynamic.com/source/xld.CFConditions.html

 

[Guest]

wOw.

If the colors are painted by Conditional Formatting then the code can
actually ignore the color and focus on the condition itself.


Consider this simple example:

Let's say cells A1 thru A100 have been condtionally formatted to be bold if
the value is greater than 10. This allows us dis-regard the boldness and
instead:

Sub LaDdIe()
IAmTheCount = 0
For i = 1 To 100
If Cells(i, "A").Value > 10 Then
IAmTheCount = IAmTheCount + 1
End If
Next
MsgBox (IAmTheCount)
End Sub


Bob Phillips does a great job of reviewing this topic in:

http://www.xldynamic.com/source/xld.CFConditions.html