Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Let Excel shade every 3 line automatically
It would be helpful for reading reports, if Excel could shade every 3rd line
automatically. It would reduce keying errors. |
#2
|
|||
|
|||
Take a look at http://www.xldynamic.com/source/xld.CF.html#rows
-- HTH RP (remove nothere from the email address if mailing direct) "BAase" wrote in message ... It would be helpful for reading reports, if Excel could shade every 3rd line automatically. It would reduce keying errors. |
#3
|
|||
|
|||
Hi Bob - Just trawling through your link, and I may be going blind but
couldn't see any mention for increasing the number of rows within the colour banding. I usually chuck out the note below for these questions, so wondered if you might want to add a couple of examples similar to the ones at the end of my note:- Use a conditional format to do this and it will not move when you sort the rows:- Do CTRL+A, then Format / Conditional Formatting / Change 'cell value is' to 'formula is' and put in =MOD(ROW(),2)=1 Now click on the format button and choose a nice pastel colour from the patterns tab. Hit OK till you are out and you are done. You could also put in =MOD(ROW(),2)=0 if you wanted the other rows to be coloured. You could also put in =MOD(ROW(),3)=0 if you wanted every 3rd row to be coloured. You could also put in =MOD(ROW(),4)=0 if you wanted every 4th row to be coloured. You could also put in =MOD(ROW(),5)=0 if you wanted every 5th row to be coloured. But you probably get the drift by now. Flipping the 1 and the 0 will determine whether or not you start with a coloured or a non-coloured row. If you want to create alternate green bars made up of say 3 rows (or any other color), you can also use: For every three rows: =MOD(ROW()-1,6)<3 For every four rows: =MOD(ROW()-1,8)<4 For other number of shadings, just make the 2nd number (6 or 8 in example) twice as much as the 3rd number (3 or 4 in example) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Bob Phillips" wrote in message ... Take a look at http://www.xldynamic.com/source/xld.CF.html#rows -- HTH RP (remove nothere from the email address if mailing direct) "BAase" wrote in message ... It would be helpful for reading reports, if Excel could shade every 3rd line automatically. It would reduce keying errors. |
#4
|
|||
|
|||
One of us must be going blind Ken :-)
This is on that link ... It is also possible to stripe every 3rd row, or every 4th row, etc. This is achieved by changing the value of the divisor argument to the 'MOD' function. So =MOD(ROW(),3)=0 will stripe every 3rd row =MOD(ROW(),4)=0 will stripe every 4th row etc. Similarly, it is simple to change which row the striping starts at. In this instance, the value that the 'MOD' function is being compared against is changed. The value tested against can be any value between 0 and the divisor minus one, as these are the only values that the 'MOD' function will return. Examples a =MOD(ROW(),2)=1 will stripe every 2nd row, starting at row 1 =MOD(ROW(),3)=1 will stripe every 3rd row, starting at row 1 =MOD(ROW(),3)=2 will stripe every 3rd row, starting at row 2 =MOD(ROW(),4)=1 will stripe every 4th row, starting at row 1 =MOD(ROW(),4)=2 will stripe every 4th row, starting at row 2 =MOD(ROW(),4)=3 will stripe every 4th row, starting at row 3 etc. The general formula is =MOD(ROW(),m+1)=MOD(n,m+1) where m is the number of non-coloured (blank) rows between coloured rows, and n is the start row. <<<<<<<<<<<<<<<<<<< Seems to cover all eventualities to me Regards Bob "Ken Wright" wrote in message ... Hi Bob - Just trawling through your link, and I may be going blind but couldn't see any mention for increasing the number of rows within the colour banding. I usually chuck out the note below for these questions, so wondered if you might want to add a couple of examples similar to the ones at the end of my note:- Use a conditional format to do this and it will not move when you sort the rows:- Do CTRL+A, then Format / Conditional Formatting / Change 'cell value is' to 'formula is' and put in =MOD(ROW(),2)=1 Now click on the format button and choose a nice pastel colour from the patterns tab. Hit OK till you are out and you are done. You could also put in =MOD(ROW(),2)=0 if you wanted the other rows to be coloured. You could also put in =MOD(ROW(),3)=0 if you wanted every 3rd row to be coloured. You could also put in =MOD(ROW(),4)=0 if you wanted every 4th row to be coloured. You could also put in =MOD(ROW(),5)=0 if you wanted every 5th row to be coloured. But you probably get the drift by now. Flipping the 1 and the 0 will determine whether or not you start with a coloured or a non-coloured row. If you want to create alternate green bars made up of say 3 rows (or any other color), you can also use: For every three rows: =MOD(ROW()-1,6)<3 For every four rows: =MOD(ROW()-1,8)<4 For other number of shadings, just make the 2nd number (6 or 8 in example) twice as much as the 3rd number (3 or 4 in example) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "Bob Phillips" wrote in message ... Take a look at http://www.xldynamic.com/source/xld.CF.html#rows -- HTH RP (remove nothere from the email address if mailing direct) "BAase" wrote in message ... It would be helpful for reading reports, if Excel could shade every 3rd line automatically. It would reduce keying errors. |
#5
|
|||
|
|||
LOL - Didn't mean that Bob, as like the text says it stripes every 3rd row
or 4th row, but that gives you say 3 white, 1 colour, 3 white, 1 colour etc. I'm talking about 3 white, 3 colour, 3 white, 3 colour etc. If you want to create alternate green bars made up of say 3 rows (or any other color), you can also use: For every three rows: =MOD(ROW()-1,6)<3 For every four rows: =MOD(ROW()-1,8)<4 For other number of shadings, just make the 2nd number (6 or 8 in example) twice as much as the 3rd number (3 or 4 in example) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- <snip |
#6
|
|||
|
|||
I'll have to weave that into the page :-)
Bob "Ken Wright" wrote in message ... LOL - Didn't mean that Bob, as like the text says it stripes every 3rd row or 4th row, but that gives you say 3 white, 1 colour, 3 white, 1 colour etc. I'm talking about 3 white, 3 colour, 3 white, 3 colour etc. If you want to create alternate green bars made up of say 3 rows (or any other color), you can also use: For every three rows: =MOD(ROW()-1,6)<3 For every four rows: =MOD(ROW()-1,8)<4 For other number of shadings, just make the 2nd number (6 or 8 in example) twice as much as the 3rd number (3 or 4 in example) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- <snip |
#7
|
|||
|
|||
Would this help?
http://www.cpearson.com/excel/banding.htm tj "BAase" wrote: It would be helpful for reading reports, if Excel could shade every 3rd line automatically. It would reduce keying errors. |
#8
|
|||
|
|||
"BAase" wrote: It would be helpful for reading reports, if Excel could shade every 3rd line automatically. It would reduce keying errors. The ASAP utilities for Excel can do this as well. I have found them to be very useful. http://www.asap-utilities.com |
#9
|
|||
|
|||
Thank you Office Spacer the utilitiies will do what i had been wanting to do
and more. -- joeq "Office Spacer" wrote: "BAase" wrote: It would be helpful for reading reports, if Excel could shade every 3rd line automatically. It would reduce keying errors. The ASAP utilities for Excel can do this as well. I have found them to be very useful. http://www.asap-utilities.com |
#10
|
|||
|
|||
Let's not forget about XL's built in banding.
I've never personally used it, but it does look functional. <Format <AutoFormat And scroll through the various choices, and then explore all the options. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "BAase" wrote in message ... It would be helpful for reading reports, if Excel could shade every 3rd line automatically. It would reduce keying errors. |
#11
|
|||
|
|||
RD
The banding in Autoformat can't deal with insert/delete rows so is basically useless for a worksheet-in-progress. Great for finished-product reports or printing. Gord On Tue, 1 Feb 2005 14:59:37 -0800, "RagDyer" wrote: Let's not forget about XL's built in banding. I've never personally used it, but it does look functional. <Format <AutoFormat And scroll through the various choices, and then explore all the options. |
#12
|
|||
|
|||
Thanks for the info Gord.
-- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "Gord Dibben" <gorddibbATshawDOTca wrote in message ... RD The banding in Autoformat can't deal with insert/delete rows so is basically useless for a worksheet-in-progress. Great for finished-product reports or printing. Gord On Tue, 1 Feb 2005 14:59:37 -0800, "RagDyer" wrote: Let's not forget about XL's built in banding. I've never personally used it, but it does look functional. <Format <AutoFormat And scroll through the various choices, and then explore all the options. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Can Excel automatically print only certain worksheets in a workboo | Excel Discussion (Misc queries) | |||
Missing values in Excel Line Chart | Charts and Charting in Excel | |||
Enter Data Into Another Excel File Automatically | Excel Worksheet Functions | |||
getting data from 2 excel sheets automatically | Excel Worksheet Functions |