| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
shading rows in excel
How do I shade alternate rows in excel 2000
|
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
shading rows in excel
You can use conditional formatting...
Highlight the section you want to have alternating bands. Select Format - Conditional Formatting... Add this formula =mod(row(), 2) = 0 Now select the desired highlighting shade using Format - Patterns Select Ok -- HTH... Jim Thomlinson "juji" wrote: How do I shade alternate rows in excel 2000 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
shading rows in excel
Select the cells/rows you want to apply this format to. Goto Format,
conditional format. Select "Formula is" from first box. Input: =MOD(ROW(),2) Select desired shading color, and hit 'ok'. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "juji" wrote: How do I shade alternate rows in excel 2000 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
shading rows in excel
If you won't be filtering the data use the other suggestions.
If you will be filtering use this formula in CFFormula =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 Gord Dibben MS Excel MVP On Fri, 7 Aug 2009 11:47:01 -0700, juji wrote: How do I shade alternate rows in excel 2000 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
shading rows in excel
BUT ALL THOSE ANSWERS GIVEN UNDER THIS HEAD COLOURS THE ENTIRE SHEET WITH ONE
COLOUR AND THE OTHER REMAINS WHITE. WHAT SHOULD I DO, IF I WANT TO FORMAT WITH TWO SEPERATE COLOURS ?? (I TRIED THE SAME WITH ADDITION OF THE CONDITION IN CONDITIONAL FORMATING AND THE SAME FORMULA, BUT IT DOEN NOT WORK) WHY ?? "Gord Dibben" wrote: If you won't be filtering the data use the other suggestions. If you will be filtering use this formula in CFFormula =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 Gord Dibben MS Excel MVP On Fri, 7 Aug 2009 11:47:01 -0700, juji wrote: How do I shade alternate rows in excel 2000 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
shading rows in excel
Condition1 =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 format as red
Condition2 =MOD(SUBTOTAL(3,$A1:$A$2),2)=1 format as green Gord On Sun, 9 Aug 2009 12:59:01 -0700, YESHWANT JOSHI wrote: BUT ALL THOSE ANSWERS GIVEN UNDER THIS HEAD COLOURS THE ENTIRE SHEET WITH ONE COLOUR AND THE OTHER REMAINS WHITE. WHAT SHOULD I DO, IF I WANT TO FORMAT WITH TWO SEPERATE COLOURS ?? (I TRIED THE SAME WITH ADDITION OF THE CONDITION IN CONDITIONAL FORMATING AND THE SAME FORMULA, BUT IT DOEN NOT WORK) WHY ?? "Gord Dibben" wrote: If you won't be filtering the data use the other suggestions. If you will be filtering use this formula in CFFormula =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 Gord Dibben MS Excel MVP On Fri, 7 Aug 2009 11:47:01 -0700, juji wrote: How do I shade alternate rows in excel 2000 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
shading rows in excel
it seems that i had not been clear to u sir
what is want is row no. 1 - one colour row no. 2 - another colour row no. 3 - one colour (same as row no. 1) row no. 4 - another colour (same as row no. 2) ............ so on "Gord Dibben" wrote: Condition1 =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 format as red Condition2 =MOD(SUBTOTAL(3,$A1:$A$2),2)=1 format as green Gord On Sun, 9 Aug 2009 12:59:01 -0700, YESHWANT JOSHI wrote: BUT ALL THOSE ANSWERS GIVEN UNDER THIS HEAD COLOURS THE ENTIRE SHEET WITH ONE COLOUR AND THE OTHER REMAINS WHITE. WHAT SHOULD I DO, IF I WANT TO FORMAT WITH TWO SEPERATE COLOURS ?? (I TRIED THE SAME WITH ADDITION OF THE CONDITION IN CONDITIONAL FORMATING AND THE SAME FORMULA, BUT IT DOEN NOT WORK) WHY ?? "Gord Dibben" wrote: If you won't be filtering the data use the other suggestions. If you will be filtering use this formula in CFFormula =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 Gord Dibben MS Excel MVP On Fri, 7 Aug 2009 11:47:01 -0700, juji wrote: How do I shade alternate rows in excel 2000 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
shading rows in excel
You were perfectly clear.
Select rows 1 to 100 then FormatConditional Formatting Condition1Formula is =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 one color Add............ Condition2Formula is =MOD(SUBTOTAL(3,$A1:$A$2),2)=1 another color You will get alternating row colors down to row 100. one color another color one color...........same as row 1 another color...........same as row 2 etc. Gord On Tue, 11 Aug 2009 08:55:05 -0700, YESHWANT JOSHI wrote: it seems that i had not been clear to u sir what is want is row no. 1 - one colour row no. 2 - another colour row no. 3 - one colour (same as row no. 1) row no. 4 - another colour (same as row no. 2) ........... so on "Gord Dibben" wrote: Condition1 =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 format as red Condition2 =MOD(SUBTOTAL(3,$A1:$A$2),2)=1 format as green Gord On Sun, 9 Aug 2009 12:59:01 -0700, YESHWANT JOSHI wrote: BUT ALL THOSE ANSWERS GIVEN UNDER THIS HEAD COLOURS THE ENTIRE SHEET WITH ONE COLOUR AND THE OTHER REMAINS WHITE. WHAT SHOULD I DO, IF I WANT TO FORMAT WITH TWO SEPERATE COLOURS ?? (I TRIED THE SAME WITH ADDITION OF THE CONDITION IN CONDITIONAL FORMATING AND THE SAME FORMULA, BUT IT DOEN NOT WORK) WHY ?? "Gord Dibben" wrote: If you won't be filtering the data use the other suggestions. If you will be filtering use this formula in CFFormula =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 Gord Dibben MS Excel MVP On Fri, 7 Aug 2009 11:47:01 -0700, juji wrote: How do I shade alternate rows in excel 2000 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
shading rows in excel
hi Gord,
i m using windows xp and office 2003. By the given formula, it is applying first colour only to all rows. what about second colour?? kindly check up at your end and inform. "Gord Dibben" wrote: You were perfectly clear. Select rows 1 to 100 then FormatConditional Formatting Condition1Formula is =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 one color Add............ Condition2Formula is =MOD(SUBTOTAL(3,$A1:$A$2),2)=1 another color You will get alternating row colors down to row 100. one color another color one color...........same as row 1 another color...........same as row 2 etc. Gord On Tue, 11 Aug 2009 08:55:05 -0700, YESHWANT JOSHI wrote: it seems that i had not been clear to u sir what is want is row no. 1 - one colour row no. 2 - another colour row no. 3 - one colour (same as row no. 1) row no. 4 - another colour (same as row no. 2) ........... so on "Gord Dibben" wrote: Condition1 =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 format as red Condition2 =MOD(SUBTOTAL(3,$A1:$A$2),2)=1 format as green Gord On Sun, 9 Aug 2009 12:59:01 -0700, YESHWANT JOSHI wrote: BUT ALL THOSE ANSWERS GIVEN UNDER THIS HEAD COLOURS THE ENTIRE SHEET WITH ONE COLOUR AND THE OTHER REMAINS WHITE. WHAT SHOULD I DO, IF I WANT TO FORMAT WITH TWO SEPERATE COLOURS ?? (I TRIED THE SAME WITH ADDITION OF THE CONDITION IN CONDITIONAL FORMATING AND THE SAME FORMULA, BUT IT DOEN NOT WORK) WHY ?? "Gord Dibben" wrote: If you won't be filtering the data use the other suggestions. If you will be filtering use this formula in CFFormula =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 Gord Dibben MS Excel MVP On Fri, 7 Aug 2009 11:47:01 -0700, juji wrote: How do I shade alternate rows in excel 2000 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
shading rows in excel
I have kindly checked up at my end and get alternately colored rows.
Office 2003 SP3 and Windows XP SP3 Gord On Tue, 11 Aug 2009 20:12:01 -0700, YESHWANT JOSHI wrote: hi Gord, i m using windows xp and office 2003. By the given formula, it is applying first colour only to all rows. what about second colour?? kindly check up at your end and inform. "Gord Dibben" wrote: You were perfectly clear. Select rows 1 to 100 then FormatConditional Formatting Condition1Formula is =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 one color Add............ Condition2Formula is =MOD(SUBTOTAL(3,$A1:$A$2),2)=1 another color You will get alternating row colors down to row 100. one color another color one color...........same as row 1 another color...........same as row 2 etc. Gord On Tue, 11 Aug 2009 08:55:05 -0700, YESHWANT JOSHI wrote: it seems that i had not been clear to u sir what is want is row no. 1 - one colour row no. 2 - another colour row no. 3 - one colour (same as row no. 1) row no. 4 - another colour (same as row no. 2) ........... so on "Gord Dibben" wrote: Condition1 =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 format as red Condition2 =MOD(SUBTOTAL(3,$A1:$A$2),2)=1 format as green Gord On Sun, 9 Aug 2009 12:59:01 -0700, YESHWANT JOSHI wrote: BUT ALL THOSE ANSWERS GIVEN UNDER THIS HEAD COLOURS THE ENTIRE SHEET WITH ONE COLOUR AND THE OTHER REMAINS WHITE. WHAT SHOULD I DO, IF I WANT TO FORMAT WITH TWO SEPERATE COLOURS ?? (I TRIED THE SAME WITH ADDITION OF THE CONDITION IN CONDITIONAL FORMATING AND THE SAME FORMULA, BUT IT DOEN NOT WORK) WHY ?? "Gord Dibben" wrote: If you won't be filtering the data use the other suggestions. If you will be filtering use this formula in CFFormula =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 Gord Dibben MS Excel MVP On Fri, 7 Aug 2009 11:47:01 -0700, juji wrote: How do I shade alternate rows in excel 2000 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
shading rows in excel
tks Gord .............. it's working now
what error i was doing is i was trying to format a blank sheet without data in it. On confirmation from u about formula working perfectly, i tried the same on a sheet and then entered / pasted data on it ........ then it worked any way, sorry for the trouble and tks for the prompt reply "Gord Dibben" wrote: I have kindly checked up at my end and get alternately colored rows. Office 2003 SP3 and Windows XP SP3 Gord On Tue, 11 Aug 2009 20:12:01 -0700, YESHWANT JOSHI wrote: hi Gord, i m using windows xp and office 2003. By the given formula, it is applying first colour only to all rows. what about second colour?? kindly check up at your end and inform. "Gord Dibben" wrote: You were perfectly clear. Select rows 1 to 100 then FormatConditional Formatting Condition1Formula is =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 one color Add............ Condition2Formula is =MOD(SUBTOTAL(3,$A1:$A$2),2)=1 another color You will get alternating row colors down to row 100. one color another color one color...........same as row 1 another color...........same as row 2 etc. Gord On Tue, 11 Aug 2009 08:55:05 -0700, YESHWANT JOSHI wrote: it seems that i had not been clear to u sir what is want is row no. 1 - one colour row no. 2 - another colour row no. 3 - one colour (same as row no. 1) row no. 4 - another colour (same as row no. 2) ........... so on "Gord Dibben" wrote: Condition1 =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 format as red Condition2 =MOD(SUBTOTAL(3,$A1:$A$2),2)=1 format as green Gord On Sun, 9 Aug 2009 12:59:01 -0700, YESHWANT JOSHI wrote: BUT ALL THOSE ANSWERS GIVEN UNDER THIS HEAD COLOURS THE ENTIRE SHEET WITH ONE COLOUR AND THE OTHER REMAINS WHITE. WHAT SHOULD I DO, IF I WANT TO FORMAT WITH TWO SEPERATE COLOURS ?? (I TRIED THE SAME WITH ADDITION OF THE CONDITION IN CONDITIONAL FORMATING AND THE SAME FORMULA, BUT IT DOEN NOT WORK) WHY ?? "Gord Dibben" wrote: If you won't be filtering the data use the other suggestions. If you will be filtering use this formula in CFFormula =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 Gord Dibben MS Excel MVP On Fri, 7 Aug 2009 11:47:01 -0700, juji wrote: How do I shade alternate rows in excel 2000 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I alternate shading of rows, Excel 2007, no new tabs. | Excel Discussion (Misc queries) | |||
| Shading rows | Excel Worksheet Functions | |||
| Shading of rows | Excel Worksheet Functions | |||
| shading rows | Excel Discussion (Misc queries) | |||
| shading different rows when a value changes in col. | Excel Worksheet Functions | |||
Linear Mode
