CODEWITHSUNDEEP
vbaexcel
AJ_91
AJ_91

Reputation: 581

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

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 :(

Upvotes: 0

Views: 2316

Answers (1)

rgo
rgo

Reputation: 491

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.

Upvotes: 1

Related Questions