Counting cells colored by conditional formating

[Safety Bill]

I'm looking for a way to count the cells in a row, that are conditionally formatted 4 different colors. Unfortunately, the 4 colors that I'm using aren't the standard Red, Green, Blue and Yellow. I have to use more pale versions.

Also, each row contains cells that are dates, text, numbers or formulas.

All I want to do is count how many cells of each color there are and have it show in a separate cell of that row (C4).

My final "Christmas Wish" is to be able to take this count and maybe use an IF formula of some type to conditionally format the cells Column C.

Here is the result I would like to achieve:

If D4:AF4 contains 1 or more pale red cells, put the count in C4 and stop.
If D4:AF4 contains 0 pale red cells, put count in C4 and proceed to next step.
If D4:AF4 contains 1 or more pale yellow cells, put the count in C4 and stop.
If D4:AF4 contains 0 pale yellow cells, put the count in C4 and proceed to next step.
And so on for the pale green and pale blues.

I've tried all the different VBA codes that I found when searching for this on the web, but to no avail. I either get an error message or 0. Please help before the safety man loses it.

 

[MARK858]

You'd need a formula for each cell that you wanted to check (a new column in your worksheet, maybe?), then you'd need another formula to sum up all the cells in that new column that returned a value of 1 (Black), 2 (White), 3 (Red) etc.

I think Pete means

=CountOfCF(A1:A10,1)

 

[NewOrderFac33]

Also note that you can use =COLORINDEXOFCF(A1,TRUE) if you want to test for the FONT as against the BACKGROUND colour.

Bear in mind that the Font and Background colours need to have been set CONDITIONALLY for this to work. If they haven't, you need to use my COLAC function as mentioned in the earlier post.

Let me know how you go on.

 

[NewOrderFac33]

Mark,

Yep, that's what I meant - I was just too lazy to come up with it!

Pete

 

[NewOrderFac33]

There's also a little macro called AA_TransferColors right at the top of the code that allows you to copy the colours generated by conditional formatting but WITHOUT the conditions that generated them - in this case, from I2:I12 to H2:H13.

Make sure you have the rest of the functionality in a module somewhere in your workbook, otherwise it won't work, as it uses ColorIndexOfCF.

Sub AA_TransferColours()
    For Each TestCell In Range("I2:I13")
        TestCell.Offset(0, -1).Interior.ColorIndex = ColorIndexOfCF(TestCell, False)
    Next
End Sub

 

[T. Valko]

Unfortunately no. That's where the "15 or more" conditions come into play. For example, one cell criteria may have 3-5 conditional rules: "Cell Value <0.9 then pale green" & "Cell Value between 0.9 and 1.1 then pale yellow" and "Cell Value> 1.1 then pale red", and Cell Value contains 'N' then pale red.

Looks like I'm not following your K.I.S.S. philosophy.

With that many conditions it would be very complicated to do with formulas.

It looks like you have a VBA solution but it's not so easy either!

 

[Safety Bill]

I'm sorry Mark and Pete. I can't seem to make it work. Thank you for you're help anyway. What do I need to do to be able to upload a file? Do I need to become a paying member or something?

 

[T. Valko]

I'm sorry Mark and Pete. I can't seem to make it work. Thank you for you're help anyway. What do I need to do to be able to upload a file? Do I need to become a paying member or something?

You can't directly upload a file to this forum.

What you can do is upload a file to either your own site or you can use a free file hosting site. Then you can post a link to the file in a reply.

If you need to use a free file hosting site there are many to choose from.

 

[Safety Bill]

Here is the link to a sample file. Hopefully this will help. Although I'm sure it's something we used to call an S.S.O. problem...Software Smarter the Operator.

http://www.mediafire.com/?odn2r0s9k1c9vjr

 

[weatherbyel]

I did this VBA and colac formula, but it is not working. I get a Microsoft Visual Basic error: Can't find project or library. When i hit ok, it list a bunch of available references. Any idea which ones i need to get this to work?
thanks!

 

[bscott05]

Sorry to bring back this old thread but i am in need of a solution of this exact problem...

I have copied the functions into a module from what Pete posted

However, when i use the formula:=colorindexofcf(O13,FALSE) it returns 14(the actual cell that is conditionally formatted is Green). When i reference to the next cell over with formula: =colorindexofcf(P13,FALSE), it returns the same value of 14 (the actual cell that is conditionally formatted is Grey)

any reason it cannot tell the differentce between the O13 cell and p13 cell's conditional format?

Any help would be appreciated!!!