Copy&pasting coditional formatting... again

[skatmandu2002]

Hiya,

I know I've made a similar post not long ago, but I've tried to apply the same logic to a different spreadsheet and can't seem to get it working.

I've attached the workbook. As you can see you can insert data into the rows under columns C-I.

If you highlight cells C6:CI, you can see there are five sets of conditional formatting per row.

Firstly are the fill colours - if you change the 'transaction type', cell C6 will change accordingly. This affects four of the conditional formatting to change the colours also accordingly.

The fifth conditional formatting checks if the date is the same as the row below it, and if it is not the same, the border between the two rows is changed to solid line.

Have a little play around with entering some data into the three rows I have formatted to get your head round what my spread sheet does...

Now the problem, I need to make the rows go down to row 2000. I've tried to use autofill and copy&pasting but that totally mucks up the conditional formatting. I've also played around with all combinations of absolute and relative columns, rows and both. But I just can't seem to get it right.

Also, is there a way to make the 'transaction type' defaulted to '(Select from list)', and that if you deleted the data, that a no entry is not allowed in that cell? I have tried going to data validation and un-ticking 'ignore blank' but no change.

Thanks! Any help on both problems would be much-appreciated.

Thanks