Sorting Problem

[siasarma]

I have made a check register with the fill colors alternating between yellow and white every other one. Then I locked the sheet with the "Select Locked Cells", "Select Unlocked Cells", "Sort", "Use Autofilter", and "Edit Objects" check boxes selected. Now my problem is when I sort the list by date at the end of the month, which I always do so I can reconcile my account easier, all the fill colors will get out of order. So, I'm just wondering if there is a way that I can lock the fill colors too, so that they always stay where they are and do not sort with the list.

Thanks!

 

[cornflakegirl]

You could do this if you assign the colours using conditional formatting.

Select cell A1, go to Format - Conditional Formatting, set Formula is to =MOD(ROW(),2)=1 and set the format to yellow. Then use the format painter to copy the format to the whole row and to all other applicable rows. The formatting will not be affected by sorting the data.

 

[Sigh]

Cornflake Girl, I was using that formula but an MVP on this board helpfully gave me a more robust formula: =MOD(ROW()-ROW(A$2)+0,2)=0. Would this be appropriate?

 

[cornflakegirl]

I don't think that formula would actually make any difference* in this circumstance, because we're only working mod2. In other formulas, that sort of robustness (ie making sure the formula still works if you insert / delete rows / columns) is essential.

*I'm prepared to be corrected on this!

 

[siasarma]

SWEET!!!! Works like a charm! Thank you!!!

 

[siasarma]

Ok, one more question. What if I want to copy and paste some of my entries into a different row. I have the formula you said, but if I copy something that is on the white row and paste it into the yellow row it will overwrite it and make it white...I did not put format the white rows...would that help? I tried really quick and it didn't seem to...

 

[cornflakegirl]

You need to put the conditional formatting on all the rows, yellow and white. The formatting belongs to the row position, rather than to the data, so however you reorder the rows, if they have the formatting, they will have the right colour.