COUNTING COLORED CELLS
 

I have a spreadsheet with 26 rows and 40 columns, some cells of which
have the background color of "tan". I need to count the colored cells
within each column. I've been going round and round with the vba
coding I found at http://www.xldynamic.com/source/xld.ColourCounter.html
- I copied the code into the vbs window, saved the module and returned
to the worksheet. In the last row of the first column, I pasted this
function: =SUMPRODUCT(--(ColorIndex(A1:A26)=3))

I know this must be very simple, but I cannot get the module to work.
One reason may be that the color is not 3 - but tan.

Any suggestions?

COUNTING COLORED CELLS
 

hi,
I think you may be right. color index 3 is red.
in vb help (alt+F11) look up the pattercolorindex property. it will show you
the delfaut colors and the corresponding index number. if you have custom
colors, then you must use the index number of the color you customized.

Regards
FSt1

"Roadmap" wrote:

I have a spreadsheet with 26 rows and 40 columns, some cells of which
have the background color of "tan". I need to count the colored cells
within each column. I've been going round and round with the vba
coding I found at http://www.xldynamic.com/source/xld.ColourCounter.html
- I copied the code into the vbs window, saved the module and returned
to the worksheet. In the last row of the first column, I pasted this
function: =SUMPRODUCT(--(ColorIndex(A1:A26)=3))

I know this must be very simple, but I cannot get the module to work.
One reason may be that the color is not 3 - but tan.

Any suggestions?

COUNTING COLORED CELLS
 

On May 25, 8:17 pm, FSt1 wrote:
hi,
I think you may be right. color index 3 is red.
in vb help (alt+F11) look up the pattercolorindex property. it will show you
the delfaut colors and the corresponding index number. if you have custom
colors, then you must use the index number of the color you customized.

Regards
FSt1



"Roadmap" wrote:
I have a spreadsheet with 26 rows and 40 columns, some cells of which
have the background color of "tan". I need to count the colored cells
within each column. I've been going round and round with the vba
coding I found athttp://www.xldynamic.com/source/xld.ColourCounter.html
- I copied the code into the vbs window, saved the module and returned
to the worksheet. In the last row of the first column, I pasted this
function: =SUMPRODUCT(--(ColorIndex(A1:A26)=3))

I know this must be very simple, but I cannot get the module to work.
One reason may be that the color is not 3 - but tan.

Any suggestions?- Hide quoted text -

- Show quoted text -

Are the dashes within the formula to be included in the formula?

COUNTING COLORED CELLS
 

hi,
not sure what you are asking. I didn't say anything about formulas or dashes.

FSt1

"Roadmap" wrote:

On May 25, 8:17 pm, FSt1 wrote:
hi,
I think you may be right. color index 3 is red.
in vb help (alt+F11) look up the pattercolorindex property. it will show you
the delfaut colors and the corresponding index number. if you have custom
colors, then you must use the index number of the color you customized.

Regards
FSt1



"Roadmap" wrote:
I have a spreadsheet with 26 rows and 40 columns, some cells of which
have the background color of "tan". I need to count the colored cells
within each column. I've been going round and round with the vba
coding I found athttp://www.xldynamic.com/source/xld.ColourCounter.html
- I copied the code into the vbs window, saved the module and returned
to the worksheet. In the last row of the first column, I pasted this
function: =SUMPRODUCT(--(ColorIndex(A1:A26)=3))

I know this must be very simple, but I cannot get the module to work.
One reason may be that the color is not 3 - but tan.

Any suggestions?- Hide quoted text -

- Show quoted text -

Are the dashes within the formula to be included in the formula?

COUNTING COLORED CELLS
 

On May 25, 9:00 pm, FSt1 wrote:
hi,
not sure what you are asking. I didn't say anything about formulas or dashes.

FSt1



"Roadmap" wrote:
On May 25, 8:17 pm, FSt1 wrote:
hi,
I think you may be right. color index 3 is red.
in vb help (alt+F11) look up the pattercolorindex property. it will show you
the delfaut colors and the corresponding index number. if you have custom
colors, then you must use the index number of the color you customized.

Regards
FSt1

"Roadmap" wrote:
I have a spreadsheet with 26 rows and 40 columns, some cells of which
have the background color of "tan". I need to count the colored cells
within each column. I've been going round and round with the vba
coding I found athttp://www.xldynamic.com/source/xld.ColourCounter.html
- I copied the code into the vbs window, saved the module and returned
to the worksheet. In the last row of the first column, I pasted this
function: =SUMPRODUCT(--(ColorIndex(A1:A26)=3))

I know this must be very simple, but I cannot get the module to work.
One reason may be that the color is not 3 - but tan.

Any suggestions?- Hide quoted text -

- Show quoted text -

Are the dashes within the formula to be included in the formula?- Hide quoted text -

- Show quoted text -

The dashes were in the formula I pasted from
http://www.xldynamic.com/source/xld.ColourCounter.html

This is what I pasted into the module: function: =SUMPRODUCT(--
(ColorIndex(A1:A26)=3))

The -- follow sumproduct(--

COUNTING COLORED CELLS
 

Plug this into your VBA module, Alt + F11, Copy from here and paste into the
module screen. If you don't see the white screen, do FileInsert Module.
Then when you get it loaded, go back to Excel, click on the cell with the
color and Then do ToolsMacroMacrosThen click on the macro name, Then click
run. The message box will show you the number you are looking for.

"Roadmap" wrote:

I have a spreadsheet with 26 rows and 40 columns, some cells of which
have the background color of "tan". I need to count the colored cells
within each column. I've been going round and round with the vba
coding I found at http://www.xldynamic.com/source/xld.ColourCounter.html
- I copied the code into the vbs window, saved the module and returned
to the worksheet. In the last row of the first column, I pasted this
function: =SUMPRODUCT(--(ColorIndex(A1:A26)=3))

I know this must be very simple, but I cannot get the module to work.
One reason may be that the color is not 3 - but tan.

Any suggestions?

COUNTING COLORED CELLS
 

I hit the post button too soon. Here is the code.

Sub whatcolorndx()
x = ActiveCell.Interior.ColorIndex
MsgBox x
End Sub

"Roadmap" wrote:

I have a spreadsheet with 26 rows and 40 columns, some cells of which
have the background color of "tan". I need to count the colored cells
within each column. I've been going round and round with the vba
coding I found at http://www.xldynamic.com/source/xld.ColourCounter.html
- I copied the code into the vbs window, saved the module and returned
to the worksheet. In the last row of the first column, I pasted this
function: =SUMPRODUCT(--(ColorIndex(A1:A26)=3))

I know this must be very simple, but I cannot get the module to work.
One reason may be that the color is not 3 - but tan.

Any suggestions?

COUNTING COLORED CELLS
 

one way, click the the cell, in the vb editor, press control-g to open the
immediate window, paste the following line and press enter

?activecell.interior.ColorIndex

it will return the colorindex
--


Gary


"Roadmap" wrote in message
ups.com...
I have a spreadsheet with 26 rows and 40 columns, some cells of which
have the background color of "tan". I need to count the colored cells
within each column. I've been going round and round with the vba
coding I found at http://www.xldynamic.com/source/xld.ColourCounter.html
- I copied the code into the vbs window, saved the module and returned
to the worksheet. In the last row of the first column, I pasted this
function: =SUMPRODUCT(--(ColorIndex(A1:A26)=3))

I know this must be very simple, but I cannot get the module to work.
One reason may be that the color is not 3 - but tan.

Any suggestions?

COUNTING COLORED CELLS
 

On May 25, 9:26 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
one way, click the the cell, in the vb editor, press control-g to open the
immediate window, paste the following line and press enter

?activecell.interior.ColorIndex

it will return the colorindex
--

Gary

"Roadmap" wrote in message

ups.com...



I have a spreadsheet with 26 rows and 40 columns, some cells of which
have the background color of "tan". I need to count the colored cells
within each column. I've been going round and round with the vba
coding I found athttp://www.xldynamic.com/source/xld.ColourCounter.html
- I copied the code into the vbs window, saved the module and returned
to the worksheet. In the last row of the first column, I pasted this
function: =SUMPRODUCT(--(ColorIndex(A1:A26)=3))

I know this must be very simple, but I cannot get the module to work.
One reason may be that the color is not 3 - but tan.

Any suggestions?- Hide quoted text -

- Show quoted text -

YEAH! Now I know the color, now I just have to try to get the formula
to count! The sumproduct doesn't work, perhaps
=COUNTYBYCOLOR(........)?

THANKS TO JLGWhiz & Gary!

COUNTING COLORED CELLS
 

you need to scroll to the bottom of the page of the link you provided. copy and
paste the colorindex function in the shaded box into a new module.
then use the sumproduct formula.


--


Gary


"Roadmap" wrote in message
oups.com...
On May 25, 9:26 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
one way, click the the cell, in the vb editor, press control-g to open the
immediate window, paste the following line and press enter

?activecell.interior.ColorIndex

it will return the colorindex
--

Gary

"Roadmap" wrote in message

ups.com...



I have a spreadsheet with 26 rows and 40 columns, some cells of which
have the background color of "tan". I need to count the colored cells
within each column. I've been going round and round with the vba
coding I found athttp://www.xldynamic.com/source/xld.ColourCounter.html
- I copied the code into the vbs window, saved the module and returned
to the worksheet. In the last row of the first column, I pasted this
function: =SUMPRODUCT(--(ColorIndex(A1:A26)=3))

I know this must be very simple, but I cannot get the module to work.
One reason may be that the color is not 3 - but tan.

Any suggestions?- Hide quoted text -

- Show quoted text -

YEAH! Now I know the color, now I just have to try to get the formula
to count! The sumproduct doesn't work, perhaps
=COUNTYBYCOLOR(........)?

THANKS TO JLGWhiz & Gary!

COUNTING COLORED CELLS
 

On May 25, 9:45 pm, Roadmap wrote:
On May 25, 9:26 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:





one way, click the the cell, in the vb editor, press control-g to open the
immediate window, paste the following line and press enter

?activecell.interior.ColorIndex

it will return the colorindex
--

Gary

"Roadmap" wrote in message

oups.com...

I have a spreadsheet with 26 rows and 40 columns, some cells of which
have the background color of "tan". I need to count the colored cells
within each column. I've been going round and round with the vba
coding I found athttp://www.xldynamic.com/source/xld.ColourCounter.html
- I copied the code into the vbs window, saved the module and returned
to the worksheet. In the last row of the first column, I pasted this
function: =SUMPRODUCT(--(ColorIndex(A1:A26)=3))

I know this must be very simple, but I cannot get the module to work.
One reason may be that the color is not 3 - but tan.

Any suggestions?- Hide quoted text -

- Show quoted text -

YEAH! Now I know the color, now I just have to try to get the formula
to count! The sumproduct doesn't work, perhaps
=COUNTYBYCOLOR(........)?

THANKS TO JLGWhiz & Gary!- Hide quoted text -

- Show quoted text -

I inserted "=COUNTBYCOLOR(BV14:CP14,40,FALSE)" - GREAT RESULTS!
Thanks so very much for your help!

COUNTING COLORED CELLS
 

The dashes are meant to be there, believe me.

Tan has a colorindex of 40 not 3.

The ColorIndex function should be in a standard code module not a sheet or
workbook module.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Roadmap" wrote in message
ups.com...
On May 25, 9:00 pm, FSt1 wrote:
hi,
not sure what you are asking. I didn't say anything about formulas or
dashes.

FSt1



"Roadmap" wrote:
On May 25, 8:17 pm, FSt1 wrote:
hi,
I think you may be right. color index 3 is red.
in vb help (alt+F11) look up the pattercolorindex property. it will
show you
the delfaut colors and the corresponding index number. if you have
custom
colors, then you must use the index number of the color you
customized.

Regards
FSt1

"Roadmap" wrote:
I have a spreadsheet with 26 rows and 40 columns, some cells of
which
have the background color of "tan". I need to count the colored
cells
within each column. I've been going round and round with the vba
coding I found
athttp://www.xldynamic.com/source/xld.ColourCounter.html
- I copied the code into the vbs window, saved the module and
returned
to the worksheet. In the last row of the first column, I pasted
this
function: =SUMPRODUCT(--(ColorIndex(A1:A26)=3))

I know this must be very simple, but I cannot get the module to
work.
One reason may be that the color is not 3 - but tan.

Any suggestions?- Hide quoted text -

- Show quoted text -

Are the dashes within the formula to be included in the formula?- Hide
quoted text -

- Show quoted text -

The dashes were in the formula I pasted from
http://www.xldynamic.com/source/xld.ColourCounter.html

This is what I pasted into the module: function: =SUMPRODUCT(--
(ColorIndex(A1:A26)=3))

The -- follow sumproduct(--