Alternate Color Lines when using Custom Views

[GLT]

Hello Everyone,

I used the following code in the Conditional Fomatting dialog box to acheive
alternate colour lines:

=MOD(ROW(),2)=1

This works fine, except when I choose a custom view, the alternate lines do
not re-color themselves and I get blocks of one color or another - looks
messy...

Does anyone know how to fix this?

Thanks

[Bob Phillips]

What is the custom view doing specifically?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"GLT" <[email protected]> wrote in message
news:[email protected]...
> Hello Everyone,
>
> I used the following code in the Conditional Fomatting dialog box to
acheive
> alternate colour lines:
>
> =MOD(ROW(),2)=1
>
> This works fine, except when I choose a custom view, the alternate lines
do
> not re-color themselves and I get blocks of one color or another - looks
> messy...
>
> Does anyone know how to fix this?
>
> Thanks

[GLT]

Hi Bob,

Thanks for your response - I have autofilter switched on for the columns and
I have saved a few different filters as a custom view...

Whenever I select a custom view and the data is filtered, the color lines
are based from the original view...

Cheers

"Bob Phillips" wrote:

> What is the custom view doing specifically?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "GLT" <[email protected]> wrote in message
> news:[email protected]...
> > Hello Everyone,
> >
> > I used the following code in the Conditional Fomatting dialog box to
> acheive
> > alternate colour lines:
> >
> > =MOD(ROW(),2)=1
> >
> > This works fine, except when I choose a custom view, the alternate lines
> do
> > not re-color themselves and I get blocks of one color or another - looks
> > messy...
> >
> > Does anyone know how to fix this?
> >
> > Thanks
>
>
>

[Bob Phillips]

Could it be because although the autofilter is hiding some rows, the MOD
function does not ignore them. Maybe this formula will work better

=MOD(SUBTOTAL(3,$A$1:$A1),2)=1

--

HTH

RP
(remove nothere from the email address if mailing direct)


"GLT" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> Thanks for your response - I have autofilter switched on for the columns
and
> I have saved a few different filters as a custom view...
>
> Whenever I select a custom view and the data is filtered, the color lines
> are based from the original view...
>
> Cheers
>
> "Bob Phillips" wrote:
>
> > What is the custom view doing specifically?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "GLT" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello Everyone,
> > >
> > > I used the following code in the Conditional Fomatting dialog box to
> > acheive
> > > alternate colour lines:
> > >
> > > =MOD(ROW(),2)=1
> > >
> > > This works fine, except when I choose a custom view, the alternate
lines
> > do
> > > not re-color themselves and I get blocks of one color or another -
looks
> > > messy...
> > >
> > > Does anyone know how to fix this?
> > >
> > > Thanks
> >
> >
> >

[David McRitchie]

If it acts like filtering and you have all cells
=MOD(SUBTOTAL(3,$A$1:$A1),2) for alternate row banding
requires all cells in column A to be used in the rows not hidden by filtering..
http://www.mvps.org/dmcritchie/excel....htm#colorband

Should be simple for you to test, I don't know what view actually does.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"GLT" <[email protected]> wrote in message news:[email protected]...
> Hello Everyone,
>
> I used the following code in the Conditional Fomatting dialog box to acheive
> alternate colour lines:
>
> =MOD(ROW(),2)=1
>
> This works fine, except when I choose a custom view, the alternate lines do
> not re-color themselves and I get blocks of one color or another - looks
> messy...
>
> Does anyone know how to fix this?
>
> Thanks

[David McRitchie]

Sorry Bob, I overlooked your reply, in fact I missed that view
was autofilter so was unsure of my answer then.

[Bob Phillips]

No problem David, it never hurts, especially with the problems people
sometimes get with not seeing some posts.

Regards

Bob


"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> Sorry Bob, I overlooked your reply, in fact I missed that view
> was autofilter so was unsure of my answer then.
>
>

[GLT]

Thanks Bob that works great

"Bob Phillips" wrote:

> No problem David, it never hurts, especially with the problems people
> sometimes get with not seeing some posts.
>
> Regards
>
> Bob
>
>
> "David McRitchie" <[email protected]> wrote in message
> news:[email protected]...
> > Sorry Bob, I overlooked your reply, in fact I missed that view
> > was autofilter so was unsure of my answer then.
> >
> >
>
>
>