How to count number of red color in a row

[vipulhumein]

i have a sheet in which many columns have conditional formatting , i want formula to count number of red background cell in a row

Note :- i am using excel 2007


thanks in advance

Regards

Vipul Jain

[Kenneth Hobs]

Conditional formatting is not the best route. Use the formula condition for red, and iterate through the cells.

[rollis13]

Also, have a look at this: http://www.cpearson.com/excel/cfcolors.htm

[vipulhumein]

hello rollis & kennethh r u...
thanks rollis again for replying my query..
cpearson site is not helpful i have already tried it...
in my sheet there are many columns in which different conditional formatting is applied...
i only want that when the cell background color changes to red then count the no. of cell background with red in a row..
please give the proper solution only for red color


regards
vipul jain

[rollis13]

If the Conditional Formating is the same for all the cells as in my attached example maybe what you ask could be done, elsewise, it looks like an impossible task as CPearson said, "Unfortunately, the Color and ColorIndex properties of a Range don't return the color of a cell that is displayed if Conditional formatting is applied to the cell. Nor does it allow you to determine whether a conditional format is currently in effect for a cell."

[vipulhumein]

hello rollis
the file which u have attached had a formula countofcf but the formula does not works why... i dont understand the attached file how it works and how it is helpful to me..

please help me out i just want that how the red color background generated from conditional formatting should be counted


Regards

Vipul Jain

[rollis13]

You need to copy to your book the CPearson's macros I placed in the code module of the my attached example.

[Kenneth Hobs]

IF you fail to take my advice, I don't know how else to help you.

To get the best help, it is often necessary to provide a short example file.

[vipulhumein]

I kenneth and rollis
Thanks for the quick feedback .
I have around 30 column in which conditional formatting is there with different condition in different column.
so please help me out.. my excel file is already become slow..as there are many sheet which have conditional formatting
please solve my query.
do u have any formula which only count red color or only count cells figure which are red in background.


Regards

Vipul Jain

[rollis13]

Please fully understand: CPearson said, "Unfortunately, the Color and ColorIndex properties of a Range don't return the color of a cell that is displayed if Conditional formatting is applied to the cell. Nor does it allow you to determine whether a conditional format is currently in effect for a cell.".
This is his get around but works only if all the cells to be counted are CF the same manner: http://www.cpearson.com/excel/cfcolors.htm (http://www.cpearson.com/excel/cfcolors.htm)

[vipulhumein]

hello rollis
This means i cant count the cell with red background color. Right
then how should i solve my query.
see i need this because i have made an automatic email received if there is any red background colored cell
so for that i only want to count red background color

Regards

Vipul Jain

[rollis13]

It is easy to count background coloured cells only if coloured manually, not CF.
As already asked by Kenneth Hobs, a dummy sample (of the original structure) could be of some help.

[vipulhumein]

sample u can use is the file which u have already attached "ex Count cells with condition colour.xls" there also the formula doesnt works
hey rollis means its impossible to count cell background color with cf
so what is the best solution for me to work please suggest me..as i have already made each and every thing for only this my work is pending

regards
Vipul Jain

[rollis13]

My attached example is still working perfectly, in column J it counts the red cells in each row. These cells are red because the CF rule is: "greater than 20000".
Please attach true file elsewise no one can be of some help, or at least, say that what you ask cannot be done.

[vipulhumein]

hi rollis the file which u have attached had a formula countofcf in column H,I,J,K results in "#value" why??? it does not count

[rollis13]

My example with CPearson's code is fully working with my Excel 2003. Sorry can't test it with newer versions of Excel.

[Aussiebear]

hi rollis the file which u have attached had a formula countofcf in column H,I,J,K results in "#value" why??? it does not count

Please attach a sample workbook. To do so, click on "Go Advanced", scroll down to "Manage Attachments" and follow the prompts from there. Its very hard to assist someone when we cannot see the workbook you are using.

Please also indicate which version of Excel you are using.

[vipulhumein]

10225please see the attachment and run the file in excel 2007 ...i am using excel 2007 version...
in excel 2007 i think count for red color background is not working...
is there any other way to count in excel 2007


regards
vipul jain

[Aussiebear]

Your CountOfCF formula is not able to count anything because you haven't asked it what it is that you want to count.
=CountOfCF($A2:$F2,1)
You named the range to count from "$A2: $F2", and you told Excel the result is to be true "1", but are you actually counting?

[vipulhumein]

sorry aussiebear but i did not understand what u want to convey me..
as i dont know much in vba

do u know how to count red background color generated from conditional formatting if yes then please help me out


Regards
Vipul Jain

[Aussiebear]

If you wish to count something, you need to do the following

CountOfCF( What to Count, The Range to count in, True or False)

You haven't told Excel what it is that you want to count.

[vipulhumein]

thanks aussiebear
for such a quick feedback
if possible please give me the proper vba to count of red color in excel 2007 version

regards
vipul jain

[Aussiebear]

What is the color index number of the cell you wish to count?

[vipulhumein]

only red color background generated from conditional formatting i wish to count

Regards
vipul jain

[Kenneth Hobs]

=COUNTIF(A2:F2,">20000")

[shrivallabha]

=COUNTIF(A2:F2,">20000")
:clap:

[rollis13]

If you wish to count something, you need to do the following

CountOfCF( What to Count, The Range to count in, True or False)

You haven't told Excel what it is that you want to count.The exact syntax is: CountOfCF( Range to count in, Rule to consider) 1-3 because Excel 2003 can have only 3 rules of CF (-1 for all rules).

[vipulhumein]

hey rollis please help me how to count only red color background in excel 2007

or else please suggest me what else i can do to get solution without modifying my sheet


Redards
Vipul jain

[vipulhumein]

hey rollis please help me how to count only red color background in excel 2007

or else please suggest me what else i can do to get solution without modifying my sheet


Redards
Vipul jain

[rollis13]

The solution has already been given more times, please understand that if you don't attach a sample of your sheet (with dummy data but exact structure or at least one entire row where the count has to be done) it will be impossible for anyone to be of any help.

[vipulhumein]

hello rollis for your ready reference please find the following attachment of my sheet. in the help sheet which has been attached there are many conditional formatting and many links from other file.
now please solve my query

Regards
Vipul jain

[rollis13]

Sorry, but as said, I only have access to Excel 2003 so can't be of any other help.
Your task looks very difficult (if not impossible) to manage but I still hope you can get help from other members.

[vipulhumein]

thanks for the feedback Rollis

Regards
Vipul Jain