LinkBack URL
About LinkBacks
Hi,
I'm trying to figure out the conditional formatting to highlight a row and automatically move it to the bottom of the spreadsheet once a date is entered in the completed column. Can anyone help with this?
Thanks,
Hi,
I'm trying to figure out the conditional formatting to highlight a row and automatically move it to the bottom of the spreadsheet once a date is entered in the completed column. Can anyone help with this?
Thanks,
Not CF, but event code. Say that the date completed column is column J, and that all the columns from A to I are filled.
1) Copy this code.
2) Right-Click the sheet tab of interest.
3) Select "View Code"
4) Paste the code into the window that appears.
5) Save the file as a macro-enabled .xlsm file.
6) Make changes to the J:J to reflect the column of dates actually used
Then when a value is entered into a single cell in that column, the entire row will be moved to the bottom of the list, based on entries in column A.
Please Login or Register to view this content.
Bernie Deitrick
Excel MVP 2000-2010
Hm it doesn't seem to be working. My completed column is G and I changed that. Not all cells in the row will necessarily be filled some may be blank. I just want a row to be moved to the bottom of the worksheet once a date is put into the completed column.
Post a sanitized version of your workbook with comments, and I will take a look.
You could also try to change the "A" in the this line:
Target.EntireRow.Copy Cells(Rows.Count, "A").End(xlUp)(2).EntireRow
to a column that you know will have values - say, D:
Target.EntireRow.Copy Cells(Rows.Count, "D").End(xlUp)(2).EntireRow
Last edited by Bernie Deitrick; 01-22-2016 at 10:06 AM.
Did you make the changes? Are there values in Column A? The code should work.
Hi,
This is the code I used:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.EntireRow.Copy Cells(Rows.Count, "G").End(xlUp)(2).EntireRow
Target.EntireRow.Delete
Application.EnableEvents = True
End Sub
I changed the J and the A to G since that column is where the completed date is entered and thats when it should move to the bottom. When I tried it, it somewhat worked. The row did move towards the bottom but it deleted another row and moved in place of that and it continued to do that whenever i entered a date into the completed column.
I've attached a generic version of my spreadsheet below
You made changes too the suggested code. This works for me on your sample.
BTW. You cannot paste an entirerow to anywhere except Column "A"Please Login or Register to view this content.
Last edited by JOHN H. DAVIS; 01-25-2016 at 02:46 PM.
The column needs to be a filled in column, where the bottom is apparent. That is why G will not work to determine the last row - my original code looked at A, and since A is filled, you should use
Please Login or Register to view this content.
Hi,
Thanks so much for your help on this again. Is there a way to conditionally format the worksheet where when the completed date is entered, it moves to the bottom of the worksheet and the row is also highlight in a light gray? I've tried formatting it a couple of different ways but i can't get the right formula.
Thanks,
You can record a macro to get the exact shade of color you want, but this will get you started.
Please Login or Register to view this content.
I actually found a similar code on another thread in this forum and tried it but the attached error came up. Same thing happened when i used the code you just gave me. Thanks
You can only have one of those macros in the module at a time - delete all but the new code that I posted.
Thank you!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks