Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 5th 05, 03:15 PM posted to microsoft.public.excel.worksheet.functions
Kjetil
 
Posts: n/a
Default Coloring rows in excel


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


  #2   Report Post  
Old December 5th 05, 03:57 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Coloring rows in excel

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

--

HTH

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


"Kjetil" wrote in
message ...

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



  #3   Report Post  
Old December 5th 05, 05:10 PM posted to microsoft.public.excel.worksheet.functions
tony h
 
Posts: n/a
Default Coloring rows in excel


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


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=490729

  #4   Report Post  
Old December 5th 05, 07:35 PM posted to microsoft.public.excel.worksheet.functions
vezerid
 
Posts: n/a
Default Coloring rows in excel

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

  #5   Report Post  
Old December 10th 05, 03:24 PM posted to microsoft.public.excel.worksheet.functions
David McRitchie
 
Posts: n/a
Default Coloring rows in excel

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" wrote in message
...

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





  #6   Report Post  
Old December 10th 05, 03:37 PM posted to microsoft.public.excel.worksheet.functions
David McRitchie
 
Posts: n/a
Default Coloring rows in excel

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" wrote in message ...
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" wrote in message
...

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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I reduce number of Rows displayed in Excel worksheet? JerryRugs Setting up and Configuration of Excel 1 November 15th 05 08:55 PM
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER MEGTOM New Users to Excel 5 October 27th 05 03:06 AM
Excel Range Value issue (Excel 97 Vs Excel 2003) Keeno Excel Discussion (Misc queries) 2 June 13th 05 02:01 PM
How do I copy filtered rows in excel BMX19 Excel Discussion (Misc queries) 1 June 3rd 05 12:27 AM
Can Excel "slide up" rows with content thru empty rows to condense portly44 Excel Worksheet Functions 2 April 1st 05 12:47 AM


All times are GMT +1. The time now is 07:28 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017