Format Spreadsheet - split text columns, rearrange columns, conditional format

Hello,

I'm trying to write a macro that will perform the following for the attached example data:

(1) Delete Column G - "Location"

(2) Split Column F - "Penetration Time" into 2 columns. One for the "Date" (Column F) and one for the "Time" (new Column G). Ideally, the date should be formatted as DD-MMM-YY. The time should be formatted HH:MM AM/PM.

(3) Column B - "Shape" should be moved to the farthest end of the data set (All other columns move to the left, this is the new Column G)

(4) New Column D - "Change" should be moved to the farthest end of the data set (all other columns move to the left, this is new Column G)

(5) If any times in the "Time" column (that is now Column E) are after 7:00am or before 10:00pm - that entire row should be deleted.

(6) If any of the numbers in the "Change" column (that is now Column G) are higher than 8, the text of that number should change to red and be bolded.

Thank you for your assistance! Ideas are greatly appreciated!

Not sure Im folling your request, Cant you show an a separate sheet your expected output

Give this a try

Please Login or Register  to view this content.

Hi Mike,

Is there a way to not have the 'new' data list in a different worksheet? (just write over the the stuff in the old worksheet?)

I was playing around with the code and I tried to just take out the 'worksheets.add' line and add some kind of ELSE line in the the IF statement to delete the rows that don't qualify (ie times that are after 7am and before 10pm). But I haven't been able to get the correct language in there. Do you have any thoughts on this?

Otherwise, it is working great! I had not even thought of this method, Thanks for that insight!

Thank you!