Originally Posted by
su_jumptd
Hello all,
Kind new at this execl stuff and help would be appricated.
Thanks in advance.
Need a couple of things here...mybe I'm reaching for the stars?
In the execl zip attached I've got a column and a couple of rows locked to prevent users from changing them. User do have permission to change the data in the cells which is how I needed to be. Question: is their a way to auto sort the data only with out altering the sheet structure and color? What I'm looking for is when a user inputs a date on the left column (including the data in that row), all the data in that row will sort by date (on the left column) automaticlly.
My other help request would be...if I shorten the sheet to about 10 rows and data is entered in the 9th row can a row (folowing the color scheme) be added automaticlly so the sheet never has one empty row at the bottom?
By the way if you need to unprotect the sheet the password is "test".
Hi,
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.
Rgds
Bookmarks