Originally Posted by su_jumptd
First create a dynamic range name called 'Shift' as follows. This will automatically extend as new rows are added.
Use the Insert Name Define option, put the word 'Shift' in the Name Box, and the following formula in the refers to box:
Then Add and close the Name box.
Now Click the whole column D and name it in the name box 'ShiftDate'
Now Add the following code to the Worksheet_Change event of sheet1 in the VBE (Visual Basic Environment). i.e. Alt-F11 to open the VBE, then find the sheet1 in the VBA Project Window on the left, double click on the Sheet1 name and in the window that opens on the right, pick the left hand drop down box, pick the Worksheet option, and then in the right hand drop down, pick the Worksheet_Change event (not the Worksheet_SelectionChange event), and add the code below:
When you enter a date in column D the range called 'Shift', the whole table will sort.
You've probably noticed that this causes the colours of the alternative cells to get out of line if the alternate sort order has changed. You need to set the conditional formats so that the pyjama stripe colours will always alternate whatever the sort. To do this format ALL the cells with one or other of teh colours.
Now go to conditional formatting for D7 and enter the following in the Formula is option.
and set the format colour of this cell to the other colour. What this does is use the conditional format when the row is an odd number. Now copy the formats of D7 to all the other cells.
I'm not sure what you mean when you say you want to avoid an empty row at the bottom. Can you explain.