Solved: Count Colors of CF Cells

[coliervile]

I have a Gantt Chart (range D8:IS378) that uses dates to fill the chart. Initially the chart fills in "blue" if the formula dates matches. The chart has two CF conditions that color the cells "red" and "blue". The two conditions looks in the columns "A" through "IS" for a "1" and then across the rows to see if there are more than two occurences and if there's a third occurences color the cell "red". I now want to count how many times a "red" cell occurs in each column to use for some other cells conditional formatting. This is the only way I can think of using since the cells that are in the range D8:IS378 don't have specific cell values.

I've found several references that talks about how to do this, one by cpearson and another found in vbaexpress- http://www.xldynamic.com/source/xld.CFConditions.html

this is the site that I've tried using, but have run into a compile error and can't figure out why. I've tried the reference three different ways (cells D2, E2 and F2) and can't seem to get it to run properly. I'm requesting your professional assistance.


Charlie

See attach file for compile error/s:

[Bob Phillips]

I have a Gantt Chart (range D8:IS378) that uses dates to fill the chart. Initially the chart fills in "blue" if the formula dates matches. The chart has two CF conditions that color the cells "red" and "blue". The two conditions looks in the columns "A" through "IS" for a "1" and then across the rows to see if there are more than two occurences and if there's a third occurences color the cell "red". I now want to count how many times a "red" cell occurs in each column to use for some other cells conditional formatting. This is the only way I can think of using since the cells that are in the range D8:IS378 don't have specific cell values.

I've found several references that talks about how to do this, one by cpearson and another found in vbaexpress- http://www.xldynamic.com/source/xld.CFConditions.html

this is the site that I've tried using, but have run into a compile error and can't figure out why. I've tried the reference three different ways (cells D2, E2 and F2) and can't seem to get it to run properly. I'm requesting your professional assistance.


Charlie

See attach file for compile error/s:

No attachment Charlie.

[coliervile]

Okay, here's the worksheet and the various ways I've tried to do the counting. Any ideas or know solutions would be great!

Charlie

[Bob Phillips]

There was a small typo on the page. I will get it corrected.

[coliervile]

Thanks "xld" for spotting it. I copied the formula directly off of the web. Just shows you that one has to be careful.

Regards
Charlie

[Bob Phillips]

Thanks "xld" for spotting it. I copied the formula directly off of the web. Just shows you that one has to be careful.

Regards
Charlie

You didn't. You tried to use SUMPRODUCT and it doesn't mention SUMPRODUCT on that page. There was amissing bracket in one line of VBA, but that was ll I could find.

[coliervile]

The SUMPRODUCT was one of the formulas I was trying. I was trying various ways that I had found during a Google search. I'm thankful that you found the missing piece of the puzzle.

Regards
Charlie

[Bob Phillips]

The SUMPRODUCT was one of the formulas I was trying. I was trying various ways that I had found during a Google search. I'm thankful that you found the missing piece of the puzzle.

Regards
Charlie
Well, I am the guilty party that wrote it! Actually, I cannot see why the SUMPRODUCT didn't work, it is exactly the technique that I use on another facility, I will need to investigate deeper.

[coliervile]

I didn't realize that you had written it. Well any way I got what I nedded with you help...job well done.

Charlie