we have spreadsheets at work which have every second line shaded with one colour and every other line shaded with another. when adding new data, how do i keep the formatting without having to highlight every second line?

Dear Elisabeth

Thank you attending Excel and PowerPoint Courses.

I am not very clear about the question but will try to answer to the best of my ability and my understanding.

If I am right you are saying that the table you have has been formatted with two different colours and when you add a new row in between the rows you want to maintain the same formatting!!

The best way to resolve this is by following these steps:

Step 1: Add the new rows and enter the data as usual
Step 2: Select the two rows that have got the two colours (Please ensure that when you select the two rows they must be together).
Step 3: Click on the Format Painter (the little paint Brush on the standard Toolbar next to the Undo Button)
Step 4: Simply select all the rows that you want to apply the formatting to.

NB: Excel will automatically maintain the same formatting i.e. the two colours for every other line (row) because you had selected the two rows with two different colours and it can follow the same pattern all the way!!

Ii hope that is what you wanted to achieve!!
If it is something else then you might want to either provide an example or even upload a sample file for us to have a look at!!

Kindest Regards

Rajeev Rawat
MOS Master Instructor 2000/2003

I have a bit of something to add to this.

You can create a worksheet with alternating coloured rows or columns (striped) with conditional formatting.

First, select the range that you want striped (or the whole sheet).

Next, go to Format/Conditional formatting. In the dropdown where it say value is, select "formula is"

In the textbox on the right type the expression "= mod(row(),2)=0" (without the quotes, of course.)

Finally, set the format that will apply to even rows.

You can vary the frequency of the stripe by using a value other than 2 in the mod function. If you want odd rows rather than even rows to be formatted, change the 0 to 1.

To have columns stripey, change the word 'row' to the word 'column'

Then you can add another conditional format to the same range of cells and the odd rows.

Maybe this will help aswell/instead?

Paul

thanks Paul - that's a great help

that's exactly what i wanted to achieve - many thanks Rajeev!!

Elisabeth

You're Welcome Elizebeth!!

I am glad that I could be of some assistance!!

Kindest Regards

Rajeev Rawat

MOS Master Instructor 2000/2003