Autoformatting...kinda?

Ok, I've created a worksheet with some alternating shaded rows using
conditional formatting and formula is: =MOD(ROW(),2). and I've
customized some cells (the same cells on every row) with formulas to do
some needed calculations when I paste data onto the worksheet from
notepad or Word, no biggie. Now here's where the problem comes in,
Everyday, there's a different amount of data, some days it's only 10
lines, some days it's 105 lines of data. Therefore i've had to make an
excess of these alternating shaded rows, and after I paste in the data,
delete the unused rows, or select everything i want printed and set
that as the print area. Is there anyway I make it so that when I paste
in the data, my alternating shaded rows(complete with my cells that i
have formulas in) will continue in there pattern to accomadate for the
number of lines of data pasted in? A bonus would be if i could make it
so that the very last row (the row after the last line of data pasted
in) is a different type of row that will sum up the vaules in the rows
pasted.

Thanks in advance


---
Message posted from http://www.ExcelForum.com/

Hi

for your shading you can just modify your conditional format formula to
=AND(MOD(ROW(),2),$a11)
assuming that your data is pasted into the first column.

Frank

Hi,

Assuming your data starts in A1

Conditional Format Condition1 (Last Line of data):
=AND($A1"",$A2="") ' Formatted Blue or whatever

Condition Format Condition2: 'Grey formatted
=AND(MOD(ROW(),2),$A1"")

Regards,

Daniel M.

"abxy " wrote in message
...
Ok, I've created a worksheet with some alternating shaded rows using
conditional formatting and formula is: =MOD(ROW(),2). and I've
customized some cells (the same cells on every row) with formulas to do
some needed calculations when I paste data onto the worksheet from
notepad or Word, no biggie. Now here's where the problem comes in,
Everyday, there's a different amount of data, some days it's only 10
lines, some days it's 105 lines of data. Therefore i've had to make an
excess of these alternating shaded rows, and after I paste in the data,
delete the unused rows, or select everything i want printed and set
that as the print area. Is there anyway I make it so that when I paste
in the data, my alternating shaded rows(complete with my cells that i
have formulas in) will continue in there pattern to accomadate for the
number of lines of data pasted in? A bonus would be if i could make it
so that the very last row (the row after the last line of data pasted
in) is a different type of row that will sum up the vaules in the rows
pasted.

Thanks in advance


---
Message posted from http://www.ExcelForum.com/

thanks, but that doesn't work. Maybe I wasn't being clear though.

Basically here's what's happening and here's what i need. The data that
i paste in starts on the 8th row, that's also where my alternating
shaded rows start, and some of the cells on everyone of those rows also
have formulas in them to perform calculations on the data that i paste
in. I need it so that every row after the 7th that contains any data in
any cells will not only follow the alternating shaded rows pattern, but
will also have those formulas in those cells that i have in every row.
But, I don't want anything in rows that have any data in any on the
cells...if that makes sense.


---
Message posted from http://www.ExcelForum.com/

Hi again

so if i understood you correctly in addition to the conditional format
you have to change your formulas in the following wax (assuming that
you paste your data in column A)

=IF(A1"",[Type in your specific formula], "")

Frank

Ok, now this actually works, Thanks a mil., but i'm not quite there yet,
This formula is only making the particular cells that I put data in
follow this pattern. I need it so that, If there are any cells with
data in them, the entire row will then follow the pattern.

Thanks


---
Message posted from http://www.ExcelForum.com/

i do not quite understand your problem. Of course you have to fill this
IF formula for the entire row. Please specify what you mena with 'any
cells'. Also an example row would help :-)

Frank

Ok, now this actually works, Thanks a mil., but i'm not quite there
yet, This formula is only making the particular cells that I put data
in follow this pattern. I need it so that, If there are any cells
with
data in them, the entire row will then follow the pattern.

Thanks


---
Message posted from http://www.ExcelForum.com/

I don't think this kind of conditional format will work as the formula
in the conditional dialog has to return either TRUE or FALSE.
What you can do if you want to check if any cell in one column (ranging
from A to L in your example is filled) is the following formula:
=AND(OR($A1""´,$B1"",$C10,$D1"",
[.......],$L1"" ),MOD(ROW();2))

This will ensure your shading. For entering this conditional format do
the following:
1. Highlight all the rows and columns for which you want to apply this
conditional format
2. Enter the formula (I assume that A1 ist your upper left corner).

Frank

Hah! I can't believe it, this is EXACTLY what I wanted. To tell the
truth I don't understand how this formula works though(possibly because
I don't know what the AND and OR functions do yet) but, heh, It
definately works!

Thanks a mil'!


---
Message posted from http://www.ExcelForum.com/

You're welcome. for AND and the OR function, quite simple:
With AND all conditions have to be fullfilled to evaluate to 'TRUE'
With OR at least one condition has to be evaluated to 'TRUE'

Frank

Hah! I can't believe it, this is EXACTLY what I wanted. To tell the
truth I don't understand how this formula works though(possibly
because I don't know what the AND and OR functions do yet) but, heh,
It definately works!

Thanks a mil'!


---
Message posted from http://www.ExcelForum.com/