Hello

I have a problem with my worksheets and would appreciate some help. I
received help on this when I set it up 5 months ago but now cannot get it to
work.

My page is split into varying blocks of cells and I want to count the number
of sales by changing the cell background to red.

This gives me a graphic picture of my sales as well as a numerical total
which is then used in further formulae.

For example, if 32 items are sold I would block out 32 cells (from a group
of 100) in RED and I want to display the total of 32 in the total sales
cell.

So....

Total Sales Cell V7 has the formula =SUMPRODUCT(--(ColorIndex(B3:U7)=3)

Total Sales Cell V11 has the formula
=SUMPRODUCT(--(ColorIndex(B9:U11)=3))

Total Sales Cell V15 has the formula
=SUMPRODUCT(--(ColorIndex(B13:U15)=3))

etc...etc....

As it did work on first attempt I presume the above is the way to go but
then, as it is not now working, is there a basic error here or a better way
to get the same result.

Many thanks

Rob

Yes, there is no ColorIndex function builtin to Excel, you have to add your
own. If you got the idea from
http://xldynamic.com/source/xld.ColourCounter.html, use the UDF provided
there.

--
HTH

Bob

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

"hollies" <robhaywood@ntlworld.com> wrote in message
news:uxuXiBm6IHA.1204@TK2MSFTNGP04.phx.gbl...
> Hello
>
> I have a problem with my worksheets and would appreciate some help. I
> received help on this when I set it up 5 months ago but now cannot get it
> to work.
>
> My page is split into varying blocks of cells and I want to count the
> number of sales by changing the cell background to red.
>
> This gives me a graphic picture of my sales as well as a numerical total
> which is then used in further formulae.
>
> For example, if 32 items are sold I would block out 32 cells (from a group
> of 100) in RED and I want to display the total of 32 in the total sales
> cell.
>
> So....
>
> Total Sales Cell V7 has the formula =SUMPRODUCT(--(ColorIndex(B3:U7)=3)
>
> Total Sales Cell V11 has the formula =SUMPRODUCT(--(ColorIndex(B9:U11)=3))
>
> Total Sales Cell V15 has the formula
> =SUMPRODUCT(--(ColorIndex(B13:U15)=3))
>
> etc...etc....
>
> As it did work on first attempt I presume the above is the way to go but
> then, as it is not now working, is there a basic error here or a better
> way to get the same result.
>
> Many thanks
>
> Rob
>
>

Thanks Bob

Now sorted it and it works when pressing F9.

One other question involving the same cells and formula. How can I get a
number in a cell to change to Bold and Black when the cell background is
changed to RED. Can find this in conditional formatting.

Cheers, Rob


"Bob Phillips" <bob.ngs@somewhere.com> wrote in message
news:OvtNz0m6IHA.1196@TK2MSFTNGP05.phx.gbl...
> Yes, there is no ColorIndex function builtin to Excel, you have to add
> your own. If you got the idea from
> http://xldynamic.com/source/xld.ColourCounter.html, use the UDF provided
> there.
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> "hollies" <robhaywood@ntlworld.com> wrote in message
> news:uxuXiBm6IHA.1204@TK2MSFTNGP04.phx.gbl...
>> Hello
>>
>> I have a problem with my worksheets and would appreciate some help. I
>> received help on this when I set it up 5 months ago but now cannot get it
>> to work.
>>
>> My page is split into varying blocks of cells and I want to count the
>> number of sales by changing the cell background to red.
>>
>> This gives me a graphic picture of my sales as well as a numerical total
>> which is then used in further formulae.
>>
>> For example, if 32 items are sold I would block out 32 cells (from a
>> group of 100) in RED and I want to display the total of 32 in the total
>> sales cell.
>>
>> So....
>>
>> Total Sales Cell V7 has the formula
>> =SUMPRODUCT(--(ColorIndex(B3:U7)=3)
>>
>> Total Sales Cell V11 has the formula
>> =SUMPRODUCT(--(ColorIndex(B9:U11)=3))
>>
>> Total Sales Cell V15 has the formula
>> =SUMPRODUCT(--(ColorIndex(B13:U15)=3))
>>
>> etc...etc....
>>
>> As it did work on first attempt I presume the above is the way to go but
>> then, as it is not now working, is there a basic error here or a better
>> way to get the same result.
>>
>> Many thanks
>>
>> Rob
>>
>>
>
>

Yes, you use the Colorindex function in the CF

=ColorIndex(A1)=3


--
__________________________________
HTH

Bob

"hollies" <robhaywood@ntlworld.com> wrote in message
news:OexQjgp6IHA.4468@TK2MSFTNGP02.phx.gbl...
> Thanks Bob
>
> Now sorted it and it works when pressing F9.
>
> One other question involving the same cells and formula. How can I get a
> number in a cell to change to Bold and Black when the cell background is
> changed to RED. Can find this in conditional formatting.
>
> Cheers, Rob
>
>
> "Bob Phillips" <bob.ngs@somewhere.com> wrote in message
> news:OvtNz0m6IHA.1196@TK2MSFTNGP05.phx.gbl...
>> Yes, there is no ColorIndex function builtin to Excel, you have to add
>> your own. If you got the idea from
>> http://xldynamic.com/source/xld.ColourCounter.html, use the UDF provided
>> there.
>>
>> --
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>> "hollies" <robhaywood@ntlworld.com> wrote in message
>> news:uxuXiBm6IHA.1204@TK2MSFTNGP04.phx.gbl...
>>> Hello
>>>
>>> I have a problem with my worksheets and would appreciate some help. I
>>> received help on this when I set it up 5 months ago but now cannot get
>>> it to work.
>>>
>>> My page is split into varying blocks of cells and I want to count the
>>> number of sales by changing the cell background to red.
>>>
>>> This gives me a graphic picture of my sales as well as a numerical total
>>> which is then used in further formulae.
>>>
>>> For example, if 32 items are sold I would block out 32 cells (from a
>>> group of 100) in RED and I want to display the total of 32 in the total
>>> sales cell.
>>>
>>> So....
>>>
>>> Total Sales Cell V7 has the formula =SUMPRODUCT(--(ColorIndex(B3:U7)=3)
>>>
>>> Total Sales Cell V11 has the formula
>>> =SUMPRODUCT(--(ColorIndex(B9:U11)=3))
>>>
>>> Total Sales Cell V15 has the formula
>>> =SUMPRODUCT(--(ColorIndex(B13:U15)=3))
>>>
>>> etc...etc....
>>>
>>> As it did work on first attempt I presume the above is the way to go but
>>> then, as it is not now working, is there a basic error here or a better
>>> way to get the same result.
>>>
>>> Many thanks
>>>
>>> Rob
>>>
>>>
>>
>>
>
>

Thanks again Bob

However, not sure I have this right.

I have selected a block of cells and entered CF and made condition 1
formula Is =ColorIndex(A1)=3 , clicked the Format button and made the font
style bold. I cannot change the size or type of font as these are greyed
out.

When I then try to apply the format, Excel crashes!!

Regards, Rob



"Bob Phillips" <BobNGs@somewhere.com> wrote in message
news:eI7UHOq6IHA.1080@TK2MSFTNGP06.phx.gbl...
> Yes, you use the Colorindex function in the CF
>
> =ColorIndex(A1)=3
>
>
> --
> __________________________________
> HTH
>
> Bob
>
> "hollies" <robhaywood@ntlworld.com> wrote in message
> news:OexQjgp6IHA.4468@TK2MSFTNGP02.phx.gbl...
>> Thanks Bob
>>
>> Now sorted it and it works when pressing F9.
>>
>> One other question involving the same cells and formula. How can I get a
>> number in a cell to change to Bold and Black when the cell background is
>> changed to RED. Can find this in conditional formatting.
>>
>> Cheers, Rob
>>
>>
>> "Bob Phillips" <bob.ngs@somewhere.com> wrote in message
>> news:OvtNz0m6IHA.1196@TK2MSFTNGP05.phx.gbl...
>>> Yes, there is no ColorIndex function builtin to Excel, you have to add
>>> your own. If you got the idea from
>>> http://xldynamic.com/source/xld.ColourCounter.html, use the UDF provided
>>> there.
>>>
>>> --
>>> HTH
>>>
>>> Bob
>>>
>>> (there's no email, no snail mail, but somewhere should be gmail in my
>>> addy)
>>>
>>> "hollies" <robhaywood@ntlworld.com> wrote in message
>>> news:uxuXiBm6IHA.1204@TK2MSFTNGP04.phx.gbl...
>>>> Hello
>>>>
>>>> I have a problem with my worksheets and would appreciate some help. I
>>>> received help on this when I set it up 5 months ago but now cannot get
>>>> it to work.
>>>>
>>>> My page is split into varying blocks of cells and I want to count the
>>>> number of sales by changing the cell background to red.
>>>>
>>>> This gives me a graphic picture of my sales as well as a numerical
>>>> total which is then used in further formulae.
>>>>
>>>> For example, if 32 items are sold I would block out 32 cells (from a
>>>> group of 100) in RED and I want to display the total of 32 in the total
>>>> sales cell.
>>>>
>>>> So....
>>>>
>>>> Total Sales Cell V7 has the formula =SUMPRODUCT(--(ColorIndex(B3:U7)=3)
>>>>
>>>> Total Sales Cell V11 has the formula
>>>> =SUMPRODUCT(--(ColorIndex(B9:U11)=3))
>>>>
>>>> Total Sales Cell V15 has the formula
>>>> =SUMPRODUCT(--(ColorIndex(B13:U15)=3))
>>>>
>>>> etc...etc....
>>>>
>>>> As it did work on first attempt I presume the above is the way to go
>>>> but then, as it is not now working, is there a basic error here or a
>>>> better way to get the same result.
>>>>
>>>> Many thanks
>>>>
>>>> Rob
>>>>
>>>>
>>>
>>>
>>
>>
>
>

CF does not allow changing of the font size or style, just bold, italic and
so on. But as to Excel crashing, I have no idea. I did try it before posting
just to confirm, and it worked fine here. What Excel do you have?

--
__________________________________
HTH

Bob

"hollies" <robhaywood@ntlworld.com> wrote in message
news:eKu8ehr6IHA.5276@TK2MSFTNGP05.phx.gbl...
> Thanks again Bob
>
> However, not sure I have this right.
>
> I have selected a block of cells and entered CF and made condition 1
> formula Is =ColorIndex(A1)=3 , clicked the Format button and made the
> font style bold. I cannot change the size or type of font as these are
> greyed out.
>
> When I then try to apply the format, Excel crashes!!
>
> Regards, Rob
>
>
>
> "Bob Phillips" <BobNGs@somewhere.com> wrote in message
> news:eI7UHOq6IHA.1080@TK2MSFTNGP06.phx.gbl...
>> Yes, you use the Colorindex function in the CF
>>
>> =ColorIndex(A1)=3
>>
>>
>> --
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "hollies" <robhaywood@ntlworld.com> wrote in message
>> news:OexQjgp6IHA.4468@TK2MSFTNGP02.phx.gbl...
>>> Thanks Bob
>>>
>>> Now sorted it and it works when pressing F9.
>>>
>>> One other question involving the same cells and formula. How can I get
>>> a number in a cell to change to Bold and Black when the cell background
>>> is changed to RED. Can find this in conditional formatting.
>>>
>>> Cheers, Rob
>>>
>>>
>>> "Bob Phillips" <bob.ngs@somewhere.com> wrote in message
>>> news:OvtNz0m6IHA.1196@TK2MSFTNGP05.phx.gbl...
>>>> Yes, there is no ColorIndex function builtin to Excel, you have to add
>>>> your own. If you got the idea from
>>>> http://xldynamic.com/source/xld.ColourCounter.html, use the UDF
>>>> provided there.
>>>>
>>>> --
>>>> HTH
>>>>
>>>> Bob
>>>>
>>>> (there's no email, no snail mail, but somewhere should be gmail in my
>>>> addy)
>>>>
>>>> "hollies" <robhaywood@ntlworld.com> wrote in message
>>>> news:uxuXiBm6IHA.1204@TK2MSFTNGP04.phx.gbl...
>>>>> Hello
>>>>>
>>>>> I have a problem with my worksheets and would appreciate some help. I
>>>>> received help on this when I set it up 5 months ago but now cannot get
>>>>> it to work.
>>>>>
>>>>> My page is split into varying blocks of cells and I want to count the
>>>>> number of sales by changing the cell background to red.
>>>>>
>>>>> This gives me a graphic picture of my sales as well as a numerical
>>>>> total which is then used in further formulae.
>>>>>
>>>>> For example, if 32 items are sold I would block out 32 cells (from a
>>>>> group of 100) in RED and I want to display the total of 32 in the
>>>>> total sales cell.
>>>>>
>>>>> So....
>>>>>
>>>>> Total Sales Cell V7 has the formula
>>>>> =SUMPRODUCT(--(ColorIndex(B3:U7)=3)
>>>>>
>>>>> Total Sales Cell V11 has the formula
>>>>> =SUMPRODUCT(--(ColorIndex(B9:U11)=3))
>>>>>
>>>>> Total Sales Cell V15 has the formula
>>>>> =SUMPRODUCT(--(ColorIndex(B13:U15)=3))
>>>>>
>>>>> etc...etc....
>>>>>
>>>>> As it did work on first attempt I presume the above is the way to go
>>>>> but then, as it is not now working, is there a basic error here or a
>>>>> better way to get the same result.
>>>>>
>>>>> Many thanks
>>>>>
>>>>> Rob
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Just a complete guess...

Try changing your printer (or at least printer driver) to something else. Maybe
it's the interaction between excel and the printer driver that's causing the
trouble.



hollies wrote:
>
> Thanks again Bob
>
> However, not sure I have this right.
>
> I have selected a block of cells and entered CF and made condition 1
> formula Is =ColorIndex(A1)=3 , clicked the Format button and made the font
> style bold. I cannot change the size or type of font as these are greyed
> out.
>
> When I then try to apply the format, Excel crashes!!
>
> Regards, Rob
>
> "Bob Phillips" <BobNGs@somewhere.com> wrote in message
> news:eI7UHOq6IHA.1080@TK2MSFTNGP06.phx.gbl...
> > Yes, you use the Colorindex function in the CF
> >
> > =ColorIndex(A1)=3
> >
> >
> > --
> > __________________________________
> > HTH
> >
> > Bob
> >
> > "hollies" <robhaywood@ntlworld.com> wrote in message
> > news:OexQjgp6IHA.4468@TK2MSFTNGP02.phx.gbl...
> >> Thanks Bob
> >>
> >> Now sorted it and it works when pressing F9.
> >>
> >> One other question involving the same cells and formula. How can I get a
> >> number in a cell to change to Bold and Black when the cell background is
> >> changed to RED. Can find this in conditional formatting.
> >>
> >> Cheers, Rob
> >>
> >>
> >> "Bob Phillips" <bob.ngs@somewhere.com> wrote in message
> >> news:OvtNz0m6IHA.1196@TK2MSFTNGP05.phx.gbl...
> >>> Yes, there is no ColorIndex function builtin to Excel, you have to add
> >>> your own. If you got the idea from
> >>> http://xldynamic.com/source/xld.ColourCounter.html, use the UDF provided
> >>> there.
> >>>
> >>> --
> >>> HTH
> >>>
> >>> Bob
> >>>
> >>> (there's no email, no snail mail, but somewhere should be gmail in my
> >>> addy)
> >>>
> >>> "hollies" <robhaywood@ntlworld.com> wrote in message
> >>> news:uxuXiBm6IHA.1204@TK2MSFTNGP04.phx.gbl...
> >>>> Hello
> >>>>
> >>>> I have a problem with my worksheets and would appreciate some help. I
> >>>> received help on this when I set it up 5 months ago but now cannot get
> >>>> it to work.
> >>>>
> >>>> My page is split into varying blocks of cells and I want to count the
> >>>> number of sales by changing the cell background to red.
> >>>>
> >>>> This gives me a graphic picture of my sales as well as a numerical
> >>>> total which is then used in further formulae.
> >>>>
> >>>> For example, if 32 items are sold I would block out 32 cells (from a
> >>>> group of 100) in RED and I want to display the total of 32 in the total
> >>>> sales cell.
> >>>>
> >>>> So....
> >>>>
> >>>> Total Sales Cell V7 has the formula =SUMPRODUCT(--(ColorIndex(B3:U7)=3)
> >>>>
> >>>> Total Sales Cell V11 has the formula
> >>>> =SUMPRODUCT(--(ColorIndex(B9:U11)=3))
> >>>>
> >>>> Total Sales Cell V15 has the formula
> >>>> =SUMPRODUCT(--(ColorIndex(B13:U15)=3))
> >>>>
> >>>> etc...etc....
> >>>>
> >>>> As it did work on first attempt I presume the above is the way to go
> >>>> but then, as it is not now working, is there a basic error here or a
> >>>> better way to get the same result.
> >>>>
> >>>> Many thanks
> >>>>
> >>>> Rob
> >>>>
> >>>>
> >>>
> >>>
> >>
> >>
> >
> >

--

Dave Peterson

Ps. There have been lots of posts that describe problems with network
printers--although these problems are speed related (changing to bold takes a
very long time for the initial change).

Maybe using a local printer would solve your problem.

(again a guess...)

hollies wrote:
>
> Thanks again Bob
>
> However, not sure I have this right.
>
> I have selected a block of cells and entered CF and made condition 1
> formula Is =ColorIndex(A1)=3 , clicked the Format button and made the font
> style bold. I cannot change the size or type of font as these are greyed
> out.
>
> When I then try to apply the format, Excel crashes!!
>
> Regards, Rob
>
> "Bob Phillips" <BobNGs@somewhere.com> wrote in message
> news:eI7UHOq6IHA.1080@TK2MSFTNGP06.phx.gbl...
> > Yes, you use the Colorindex function in the CF
> >
> > =ColorIndex(A1)=3
> >
> >
> > --
> > __________________________________
> > HTH
> >
> > Bob
> >
> > "hollies" <robhaywood@ntlworld.com> wrote in message
> > news:OexQjgp6IHA.4468@TK2MSFTNGP02.phx.gbl...
> >> Thanks Bob
> >>
> >> Now sorted it and it works when pressing F9.
> >>
> >> One other question involving the same cells and formula. How can I get a
> >> number in a cell to change to Bold and Black when the cell background is
> >> changed to RED. Can find this in conditional formatting.
> >>
> >> Cheers, Rob
> >>
> >>
> >> "Bob Phillips" <bob.ngs@somewhere.com> wrote in message
> >> news:OvtNz0m6IHA.1196@TK2MSFTNGP05.phx.gbl...
> >>> Yes, there is no ColorIndex function builtin to Excel, you have to add
> >>> your own. If you got the idea from
> >>> http://xldynamic.com/source/xld.ColourCounter.html, use the UDF provided
> >>> there.
> >>>
> >>> --
> >>> HTH
> >>>
> >>> Bob
> >>>
> >>> (there's no email, no snail mail, but somewhere should be gmail in my
> >>> addy)
> >>>
> >>> "hollies" <robhaywood@ntlworld.com> wrote in message
> >>> news:uxuXiBm6IHA.1204@TK2MSFTNGP04.phx.gbl...
> >>>> Hello
> >>>>
> >>>> I have a problem with my worksheets and would appreciate some help. I
> >>>> received help on this when I set it up 5 months ago but now cannot get
> >>>> it to work.
> >>>>
> >>>> My page is split into varying blocks of cells and I want to count the
> >>>> number of sales by changing the cell background to red.
> >>>>
> >>>> This gives me a graphic picture of my sales as well as a numerical
> >>>> total which is then used in further formulae.
> >>>>
> >>>> For example, if 32 items are sold I would block out 32 cells (from a
> >>>> group of 100) in RED and I want to display the total of 32 in the total
> >>>> sales cell.
> >>>>
> >>>> So....
> >>>>
> >>>> Total Sales Cell V7 has the formula =SUMPRODUCT(--(ColorIndex(B3:U7)=3)
> >>>>
> >>>> Total Sales Cell V11 has the formula
> >>>> =SUMPRODUCT(--(ColorIndex(B9:U11)=3))
> >>>>
> >>>> Total Sales Cell V15 has the formula
> >>>> =SUMPRODUCT(--(ColorIndex(B13:U15)=3))
> >>>>
> >>>> etc...etc....
> >>>>
> >>>> As it did work on first attempt I presume the above is the way to go
> >>>> but then, as it is not now working, is there a basic error here or a
> >>>> better way to get the same result.
> >>>>
> >>>> Many thanks
> >>>>
> >>>> Rob
> >>>>
> >>>>
> >>>
> >>>
> >>
> >>
> >
> >

--

Dave Peterson