• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count rows highlighted in red color

You might start here, may or may not suit your needs but Chip Pearson seems to be the color counting guru among MVP's.


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


If the rows are NOT conditional format colored, it's pretty much duck soup with VBA.


Regards,

Howard
 
Nishant


You can't do what you want without VBA


If the colors are set manually according to some criteria you can use the same logic in a formula to do what you want
 
Thanks for you reply, but i can't use VBA in my sheet because the person who will use this sheet neither know excel nor VBA

& this red color are manually filled by team member

Thanks again
 
Nishant


To use VBA all the person needs to know is How to approve the use of VBA when Excel starts

After that it can be full automated


Would you now like to see a solution ?
 
Good day Hui


Goel may not make a request to see solution but I will and I an sure others would like to see it as well, I have been looking on the web for an answer to a similar problem, it would seem that all answers point to a bewildering array of VBA answers. And from looking through the fog of VBA code it all so points to the fact that the code you need depends on whether you have used fill cell to get the colour or have coloured the cells via CF
 
Hi Goel,


Assuming your Color Cells are in Column A and Column B is blank


* Name Manager > New

* Name : Color_cell

* Refers to : =GET.CELL(63,OFFSET(INDIRECT("A1"),ROW()-1,0))

* Now in B1 Write Formula as =Color_cell

* I hope now you can manage the count part..


Regards,

Deb
 
@Debraj Roy

Hi!

Nice trick, I assume it works, so, chapeau, Monsieur!

Regards!

PS: and I assume that it doesn't work with CF, or it does?
 
Hi SirJB007,

No.. Its only with
Code: 
.Interior.ColorIndex

and failed in case of .DisplayFormat.Interior.ColorIndex
or ConditionalFormat

BTW.. OP has confirmed that

this red color are manually filled by team member
Click to expand...

Regards,

Deb
 
Hi Deb,


What am I doing wrong?


I did all this:

* Name Manager > New

* Name : Color_cell

* Refers to : =GET.CELL(63,OFFSET(INDIRECT("A1"),ROW()-1,0))

* Now in B1 Write Formula as =Color_cell


Fill A1 red, select B1 click in the formula box then hit enter returns 3.


Clear Col A, then fill A2, A5, A10 red, click in the formula box then hit enter returns 0.


Howard
 
Hi Howard..


Drag B1 toward Down.. Every cell has same formula
Code: 
=ColorCell

I mean B2,B5 and B10 also need formula =Color_Cell

For each cell it will return its.. ColorIndex.. i.e 3 is red and 6 is Yellow..


Regards,

Deb
 
Goel

Get.Cell is an old Excel 4 macro command

It is still supported in Excel up to v2013 but isn't guaranteed in the future

It can only be used in a Named Formula
 
You must log in or register to reply here.
Back
Top