• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Coloured rows

T

the_observer

Member
Hi all.

I am a newbie so the best best place for help is here i think.

I would like to make the rows of a workbook in xl 2007 look like below :
excel01.JPG


Is this possible?
If yes how?

Thank you much for your time reading this.

Regards,
The_Observer.
 
Hi Observer

It looks like a Table to me. Go to Insert – Table – then select the range the table relates to and click enter. If you are after Conditional Formatting then click on Conditional Formatting and New Rule use a formula

=MOD(ROW(),2)=0

For the odd rows use this

=MOD(ROW()-1,2)=0


Should get you what you want. I will attach a file if I get a moment.


Take care

Smallman
 

Attachments

  • ColourEverySecondRow.xls
    27.5 KB · Views: 5
Good day Observer

If you turn your data into a table a new tab will appear in the ribbon, "Table Tools/Design" which will let you choose many different styles of "Zebra striping".

If the row colour combination you want is not there it takes a few seconds to create your own, select the whole of the first row and apply a colour, select your second whole row and do the same (if you only need a certain number of columns then select them and apply fill colour this would be better than whole rows as colour filling whole rows would take up quite a bit of memory), do the same for more rows column as you need.

When you have your rows, two, three, four, then select them all or the area if you have just chosen a number of columns/rows, select format painter click the first left most cell under your row selection and then drag down and to the right to fill all the columns and rows you need.

Takes longer to read this post than to do it.
 
Whaoo!
That was really quick and precise.I am really impressed.
Thank you all for your answers.

Now i have another question.

Since i dont like to use a table i copy a piece of the zebra colored cells and copy it to another workbook.
Then if i insert a row it takes the same color of the above row.
Is it possible that it will rearrange the color of the colored rows if i enter or delete a row in order to keep the zebra coloring?
Or this is only happens when i work in a table?

Is there another way to have this color in cells without using table?

Thank you for your time reading this.

Regards,
The_Observer.
 
Hi

This;
Is it possible that it will rearrange the color of the colored rows if i enter or delete a row in order to keep the zebra coloring?
Click to expand...

On the Conditionalformatting tab if you delete a row the Zebra colouring is kept. I only have the range for the first 10 rows but this was an example for you to work with.

Take care

Smallman
 
Hi ,

Just as an aside , if the first CF formula ( the one on top ) is :

=MOD(ROW(),2)=0

the one below can be :

=1

Narayan
 
NARAYANK991 said:
Hi ,

Just as an aside , if the first CF formula ( the one on top ) is :

=MOD(ROW(),2)=0

the one below can be :

=1

Narayan
Click to expand...

I really have no idea what this is for. Be patient with me i am so newbie :(

I also have another question.
Is it possible that i see grid lines?
I ticked the option in the View->Gridlines but still does not show them around the area where i apply the Conditional formatting you suggest.

Thank you for your time reading this.

Regards,
The_Observer.
 
Hi ,

Check the uploaded file.

The dark lines you want are from BORDERS , not gridlines.

Narayan
 

Attachments

  • Example_Observer.xlsx
    7.1 KB · Views: 4
Yes ,

that is exactly what i need.

Thank you much Narayank.

Now ,your other comment about :

Just as an aside , if the first CF formula ( the one on top ) is :

=MOD(ROW(),2)=0

the one below can be :

=1

what is it?
Is it something i should know or ignore it?

Thank you for your time reading this.

Regards,
The_Observer.
 
Hi ,

Sorry if I have confused matters ! My post was in the context of Marcus Small's post regarding the following two formulae :

For the even rows :
=MOD(ROW(),2)=0

For the odd rows use this
=MOD(ROW()-1,2)=0
Smallman
Click to expand...
If you order the two formula so that the formula which is executed first is : =MOD(ROW(),2)=0 , then the second one can be just =1.

You can ignore it.

Narayan
 
Hi all.

After the successful resolve of the colored rows question in the above posts :)

i would like to ask if it is possible somehow that i can have the conditional formatting rule talked above set by default when i open a new workbook or sheet.

Thank you for your time reading this.

Regards,
the_observer.
 
I don't get your question TO. When you open the spreadsheet I attached the CF appears. So it will always appear by default when you set the CF.

Can you clarify?

Ta

Smallman
 
Sorry i was not clear.

I mean ,when i create a new workbook not one already existing like yours smallman.

Of course smallman,i could use your document every time i want to create a new excel.
Or i could insert the conditional formatting rules every time i create a new document.

I am just wondering if this can be set to be fixed somehow so when i create a new excel these coonditional formatting rules to be applied automatically.

I guess this can not be done,right?

Thank you for your concern,

Regards,
the_observer.
 
You must log in or register to reply here.
Back
Top