counting non-blank cells

[rebecca g]

Hello,
I am using the COUNT A function that allows you to count non-blank cells. The cells that I am counting are highlighted in three different colors. I would actually like to count non-blank cells that are two of those three colors and not count the ones that are the third color. For example, count the yellow and blue ones, but not the red ones.

Is there a way to make my formula =COUNTA(A1:A10) do that?

Thanks,
Rebecca

 

[Jonmo1]

Only in excel 2007.

However, if those cells are colored VIA conditional formatting, the criteria in the conditional formatting could be used to accomplish that.

 

[ageren]

copy the code below in your macro module ...

sub count_the_colors ()

[b1] = 0
last = [a65000].end(3).row
for i = 1 to last
if range("A"& i).interior.colorindex = 6 or _
range("A" & i ).interior.colorindex = 11 then
[b1] = [b1] + 1
end if
next i

end sub

 

[rebecca g]

Jonmo1: I only have Excel 2003. But you do mention that it can be done using conditional formatting. How would I do that?

Ageren: I wish I could better understand the advice you gave. I dont have a macro module and never use code. Should I?

 

[ageren]

OK. let's start

open your worksheet > click tools > click macro > click visual basic editor > click insert (at the VB editor window) > click module > copy above codes into blank page written by me > press F5

you will see the result at B1 cell....

 

[Scott Huish]

How are the cells colored, manually changing them or with Conditional Formatting?

 

[rebecca g]

right now I am manually changing them because they change throughout the spreadsheet since the spreadsheet is a schedule and a colored cell relates to being on call. It changes all the time. Do you think this is something I should/could do with conditional formatting?

I am still trying to work through ageren's macro. working to apply it to my spreadsheet. for the macro to work, do the cells need to be colored through conditional formatting?

 

[ageren]

for the macro to work, do the cells need to be colored through conditional formatting?

No. Actually, they do not have to be painted through conditional formatting.. Otherwise, macro code won't work...