gray out and move completed items to different worksheet

Hi! Alright so I'm an Excel novice and I need help with something

Essentially, I am creating an Excel Document that is created as a "to-do list" for my boss for her to enter in mine and my fellow employees' tasks for the day/week/etc. I have done quite a bit of editing to the excel doc (so proud! haha) and am almost where I want it to be-- just a few things missing though.

In one of the columns, you can select the "status" of the task-- I have entered in a formula (found online-- it worked!) that makes it so once the status is changed to "done", it will delete itself from the main sheet and add itself to Sheet 3 (completed).

Is there any way to have it do the following instead...? -- when the status is changed to "done", the entire row will grey out (the background of the cells in that row will turn grey), and the task will move to the bottom of all the rows? then, furthermore, each employee will have their own Sheet- when a task is entered by my boss, the task will show in both Sheet 1 (master list) and move itself to its respective sheet, based upon the person?

For instance, if she enters in a task and chooses Kirsten (me), the task will appear on Sheet 1 as well as whatever sheet I have designated to myself (let's say sheet 3). Then, when I go into the doc and select "done" --either from the master or my designated sheet-- the task will grey itself out and move to the bottom of both the master list and my list?

Wow- I hope this made sense!!

Thanks so so much for any and all help!
Kirsten

Hi Kirsten,

I'm going to get you halfway to your goal (highlighting gray then moving to bottom). I can take a look at the second part of the request (moving to sheets) later if no one else tackles it, but I'm a bit pressed for time now.

The following code, which is really just an amendment of the code you've developed/used, will highlight DONE fields gray then move them to the bottom. Let me know if it works as Expected.

Also, just a note. In your attached example, there are dashes in cells H12:H25. You should delete these dashes prior to running the code as they can throw things off.

Please Login or Register  to view this content.

Thank you so much!!

So, I copied the code (replaced the old code with the one you provided), and what it does is grey out the row once I select "done"-- then moves it to the top of the sheet (is there a way to move it to the bottom instead?). Then, the next time I select done for an item, it moves that one to the top of the sheet and the previous "done" row disappears (not sure where it goes?-- is there a way for it to stay there?). Thanks again!

Hi Kristen,

I made a slight change to the code, and it should be working now. Use this code, and let me know if it works as requested.

Please Login or Register  to view this content.

EDIT to add: The code as it exists queries COLUMN A to determine the last used row. With that said, if there are rows that include data in say columns B:E, but A is blank, the code would not recognize these rows. With that in mind, is there a specific column that will ALWAYS be populated with data if a row is in use? If so, we are likely better off querying that column.