Coloring rows in excel

[Kjetil]

I have a worksheet with 10.000 rows, and I would like to colorize every other
"group" of rows. A bit hard to explain, but here is an example:
1 <-- Gray
1 <-- Gray
1 <-- Gray
2 <-- White
2 <-- white
4 <-- Gray
4 <-- Gray
4 <-- Gray

Does anybody have a good idea on how to do this automatically?

[Bob Phillips]

See http://www.xldynamic.com/source/xld.CF.html#rows

--

HTH

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


"Kjetil" <[email protected]> wrote in
message news:[email protected]...
>
> I have a worksheet with 10.000 rows, and I would like to colorize every
> other
> "group" of rows. A bit hard to explain, but here is an example:
> 1 <-- Gray
> 1 <-- Gray
> 1 <-- Gray
> 2 <-- White
> 2 <-- white
> 4 <-- Gray
> 4 <-- Gray
> 4 <-- Gray
>
> Does anybody have a good idea on how to do this automatically?
>
>
> --
> Kjetil
> ------------------------------------------------------------------------
> Kjetil's Profile:
http://www.excelforum.com/member.php...o&userid=29362
> View this thread: http://www.excelforum.com/showthread...hreadid=490729
>

[tony h]

I tend to use conditional formatting for this - to keep it truely dynamic. Or write a piece of VBA as the overhead is lower.

You can either do the testing as a formula in the conditional formatting or create a new colum using a formula such as =IF(A2=A1,B1,NOT(B1)) (the new column is B) this alternates between TRUE and FALSE for each GROUP. Then it is a simple matter to set the conditional formatting to use white for TRUE and Grey for False or whatever. The formatting can be applier to each affected column.

hope this helps

[vezerid]

Kjetil,
the confusing thing here is the reasoning behind the grouping. Is it
three gray rows followed by two white ones? What are the numbers 1, 2,
4 in the first column? Do you want to change color each time the number
changes? Please be more specific.

Regards,
Kostis Vezerides

[David McRitchie]

Hi Kjetil,
See Color Grouping with alternating colors (#grouping)
Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

What Bob supplied will definitely not work, though you
wording could have mentioned based on value in column A,
but the example is fairly clear as long as not interpreted to
3 of a color then 2 of a color.
---
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

"Kjetil" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet with 10.000 rows, and I would like to colorize every
> other
> "group" of rows. A bit hard to explain, but here is an example:
> 1 <-- Gray
> 1 <-- Gray
> 1 <-- Gray
> 2 <-- White
> 2 <-- white
> 4 <-- Gray
> 4 <-- Gray
> 4 <-- Gray
>
> Does anybody have a good idea on how to do this automatically?
>
>
> --
> Kjetil
> ------------------------------------------------------------------------
> Kjetil's Profile: http://www.excelforum.com/member.php...o&userid=29362
> View this thread: http://www.excelforum.com/showthread...hreadid=490729
>

[David McRitchie]

My example might be unclear for you so if column E is the helper
column and you only have column A that you are interested in
values to identify a group. The formula in the help column for use
in the conditional formatting would be

E1: 0
E2: =MOD(OFFSET($E2,-1,0)+ ($A2<>OFFSET($A2,-1,0)),2)

Fill down from E2 with the fill handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm


--
---
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

"David McRitchie" <[email protected]> wrote in message news:%[email protected]...
> Hi Kjetil,
> See Color Grouping with alternating colors (#grouping)
> Conditional Formatting
> http://www.mvps.org/dmcritchie/excel/condfmt.htm
>
> What Bob supplied will definitely not work, though you
> wording could have mentioned based on value in column A,
> but the example is fairly clear as long as not interpreted to
> 3 of a color then 2 of a color.
> ---
> 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
>
> "Kjetil" <[email protected]> wrote in message
> news:[email protected]...
> >
> > I have a worksheet with 10.000 rows, and I would like to colorize every
> > other
> > "group" of rows. A bit hard to explain, but here is an example:
> > 1 <-- Gray
> > 1 <-- Gray
> > 1 <-- Gray
> > 2 <-- White
> > 2 <-- white
> > 4 <-- Gray
> > 4 <-- Gray
> > 4 <-- Gray
> >
> > Does anybody have a good idea on how to do this automatically?
> >
> >
> > --
> > Kjetil
> > ------------------------------------------------------------------------
> > Kjetil's Profile: http://www.excelforum.com/member.php...o&userid=29362
> > View this thread: http://www.excelforum.com/showthread...hreadid=490729
> >
>
>