Counting coloured cells doesn't work with conditional formatting in Excel

0
votes

Trying to count the number of certain coloured cells on my worksheet using this peice of VBA code : 

Function CountRed(MyRange)
CountRed = 0
For Each Cell In MyRange
If Cell.Interior.Color = RGB(255, 0, 0) Then
CountRed = CountRed + 1
End If
Next Cell
End Function

Basically, counts the number of red cells. Now this works fine if I colour them myself but if i put conditional formatting in my worksheet to colour these cells it doesnt work.

Here is my condition : 

=AND(NOT(ISBLANK(A3)),ISBLANK(D3))

Basically if the first cell is populated and this one is not then colour it red.

Now when I use my 'CountRed' function to see if this cell (the one with the conditional formatting) is red it doesnt work, but when I 'overwrite' it, i.e fill it Red myself and run the function again it works.

I thought maybe it's to do with the conditional formatting being called before the CountRed function but I didn't have any luck :(

1
vbaexcel
CF doesn't alter the Interior.Color property. You'd be better off using a formula that counts the cells matching your conditions. From 2010 there is a Displayformat property that reflects the displayed format but it won't work in a UDF called from a cell. – Rory
Ah well i didnt know that did i. Thanks for the help, pointed me in the right direction :)) – AJ_91

1 Answers

1
votes

That's because conditional formatting is not part of the Interior.color collection and as such cannot be read or counted. Depending on what the underlying value is in the cell with the conditional formatting you can count the literal values instead.

For example, set the cell values to Red, Green, Yellow but set the conditional formatting to set the background and foreground colors the same. (i.e. red on red, green on green, etc.) This way you have something to count. Use the values you are interpreting to get to the conditional color as the column to count by.