shading rows in excel

  • Thread starter Thread starter juji
  • Start date Start date
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
 
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'.
 
If you won't be filtering the data use the other suggestions.

If you will be filtering use this formula in CF>Formula

=MOD(SUBTOTAL(3,$A1:$A$2),2)=0


Gord Dibben MS Excel MVP
 
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 ??
 
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
 
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
 
You were perfectly clear.

Select rows 1 to 100 then Format>Conditional Formatting

Condition1>Formula is =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 one color

Add............

Condition2>Formula 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
 
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.
 
I have kindly checked up at my end and get alternately colored rows.

Office 2003 SP3 and Windows XP SP3


Gord
 
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
 
Back
Top