Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reduce number of Rows displayed in Excel worksheet? | Setting up and Configuration of Excel | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
How do I copy filtered rows in excel | Excel Discussion (Misc queries) | |||
Can Excel "slide up" rows with content thru empty rows to condense | Excel Worksheet Functions |