Macro function which finds and replaces cells

[cpisthedbb]

Hi,

I've been trying for a while to do this but my knowledge on macros is limited so would appreciate any help.

I'm copying from a database into Excel process times. With some of the process times there has been an adjustment so the original time needs altering. For example the 1st 2 lines need amending so the end time for Daniels is 06/12/18 06:30 altering his duration. The adjustments can sometimes be before the one that needs altering or after which is why I'm struggling to compile a macro which does the following:

- If there is a minus adjustment, this needs deductiong from the original time to show the correct duration.
- Once this has been done the minus line needs deleting
- any until time which has been amended needs then turning green to show the adjustment.

The second post is what I was after the spreadsheet looking like.

(I hope all this makes sense and hope there's a way of doing it)



Original Spreadsheet:

A B C D E F

[TABLE="width: 665"]
<colgroup><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]User ID[/TD]
[TD]Name[/TD]
[TD]From[/TD]
[TD]Until[/TD]
[TD]Capacity [%][/TD]
[TD]Duration [h][/TD]
[/TR]
[TR]
[TD]2021[/TD]
[TD]Daniels[/TD]
[TD="align: right"]06/12/2018 04:15[/TD]
[TD="align: right"]06/12/2018 06:56[/TD]
[TD][/TD]
[TD="align: right"]02:41:05[/TD]
[/TR]
[TR]
[TD]2021[/TD]
[TD]Daniels[/TD]
[TD="align: right"]06/12/2018 06:30[/TD]
[TD="align: right"]06/12/2018 06:56[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]00:26:13[/TD]
[/TR]
[TR]
[TD]3007[/TD]
[TD]Rhodes[/TD]
[TD="align: right"]05/12/2018 04:24[/TD]
[TD="align: right"]05/12/2018 07:17[/TD]
[TD][/TD]
[TD="align: right"]02:52:56[/TD]
[/TR]
[TR]
[TD]3007[/TD]
[TD]Rhodes[/TD]
[TD="align: right"]05/12/2018 06:00[/TD]
[TD="align: right"]05/12/2018 07:17[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]01:17:04[/TD]
[/TR]
[TR]
[TD]3007[/TD]
[TD]Rhodes[/TD]
[TD="align: right"]07/12/2018 18:03[/TD]
[TD="align: right"]07/12/2018 21:07[/TD]
[TD][/TD]
[TD="align: right"]03:04:00[/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]Slack[/TD]
[TD="align: right"]05/12/2018 02:16[/TD]
[TD="align: right"]05/12/2018 03:01[/TD]
[TD][/TD]
[TD="align: right"]00:44:47[/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]Slack[/TD]
[TD="align: right"]05/12/2018 04:28[/TD]
[TD="align: right"]05/12/2018 07:18[/TD]
[TD][/TD]
[TD="align: right"]02:49:04[/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]Slack[/TD]
[TD="align: right"]05/12/2018 05:59[/TD]
[TD="align: right"]05/12/2018 07:18[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]01:19:02[/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]Slack[/TD]
[TD="align: right"]07/12/2018 18:02[/TD]
[TD="align: right"]07/12/2018 18:08[/TD]
[TD][/TD]
[TD="align: right"]00:05:35[/TD]
[/TR]
[TR]
[TD]3013[/TD]
[TD]Jagger[/TD]
[TD="align: right"]07/12/2018 15:31[/TD]
[TD="align: right"]07/12/2018 17:54[/TD]
[TD][/TD]
[TD="align: right"]02:23:01[/TD]
[/TR]
[TR]
[TD]3014[/TD]
[TD]Bowes[/TD]
[TD="align: right"]03/12/2018 03:29[/TD]
[TD="align: right"]03/12/2018 06:53[/TD]
[TD][/TD]
[TD="align: right"]03:23:36[/TD]
[/TR]
[TR]
[TD]3014[/TD]
[TD]Bowes[/TD]
[TD="align: right"]03/12/2018 06:00[/TD]
[TD="align: right"]03/12/2018 06:53[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]00:53:22[/TD]
[/TR]
[TR]
[TD]3014[/TD]
[TD]Bowes[/TD]
[TD="align: right"]05/12/2018 18:02[/TD]
[TD="align: right"]05/12/2018 19:42[/TD]
[TD][/TD]
[TD="align: right"]01:39:30[/TD]
[/TR]
[TR]
[TD]3014[/TD]
[TD]Bowes[/TD]
[TD="align: right"]07/12/2018 03:35[/TD]
[TD="align: right"]07/12/2018 06:43[/TD]
[TD][/TD]
[TD="align: right"]03:08:15[/TD]
[/TR]
[TR]
[TD]3014[/TD]
[TD]Bowes[/TD]
[TD="align: right"]07/12/2018 06:00[/TD]
[TD="align: right"]07/12/2018 06:43[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]00:43:16[/TD]
[/TR]
[TR]
[TD]3015[/TD]
[TD]Selways[/TD]
[TD="align: right"]04/12/2018 06:43[/TD]
[TD="align: right"]04/12/2018 07:34[/TD]
[TD][/TD]
[TD="align: right"]00:50:26[/TD]
[/TR]
[TR]
[TD]3015[/TD]
[TD]Selways[/TD]
[TD="align: right"]04/12/2018 07:34[/TD]
[TD="align: right"]04/12/2018 09:46[/TD]
[TD][/TD]
[TD="align: right"]02:11:56[/TD]
[/TR]
[TR]
[TD]3016[/TD]
[TD]Holborn[/TD]
[TD="align: right"]09/12/2018 00:41[/TD]
[TD="align: right"]09/12/2018 02:56[/TD]
[TD][/TD]
[TD="align: right"]02:14:54[/TD]
[/TR]
[TR]
[TD]3016[/TD]
[TD]Holborn[/TD]
[TD="align: right"]09/12/2018 03:21[/TD]
[TD="align: right"]09/12/2018 07:03[/TD]
[TD][/TD]
[TD="align: right"]03:41:57[/TD]
[/TR]
[TR]
[TD]3016[/TD]
[TD]Holborn[/TD]
[TD="align: right"]09/12/2018 06:00[/TD]
[TD="align: right"]09/12/2018 07:03[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]01:03:25[/TD]
[/TR]
[TR]
[TD]3019[/TD]
[TD]Lovells[/TD]
[TD="align: right"]09/12/2018 00:42[/TD]
[TD="align: right"]09/12/2018 02:57[/TD]
[TD][/TD]
[TD="align: right"]02:15:09[/TD]
[/TR]
[TR]
[TD]3019[/TD]
[TD]Lovells[/TD]
[TD="align: right"]09/12/2018 06:00[/TD]
[TD="align: right"]09/12/2018 07:01[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]01:01:17[/TD]
[/TR]
[TR]
[TD]3019[/TD]
[TD]Lovells[/TD]
[TD="align: right"]09/12/2018 03:20[/TD]
[TD="align: right"]09/12/2018 07:01[/TD]
[TD][/TD]
[TD="align: right"]03:40:46[/TD]
[/TR]
</tbody>[/TABLE]

 

[cpisthedbb]

[TABLE="width: 531"]
<colgroup><col><col span="3"><col span="2"></colgroup><tbody>[TR]
[TD]User ID[/TD]
[TD]Name[/TD]
[TD]From[/TD]
[TD]Until[/TD]
[TD]Capacity [%][/TD]
[TD]Duration [h][/TD]
[/TR]
[TR]
[TD]2021[/TD]
[TD]Daniels[/TD]
[TD="align: right"]06/12/2018 04:15[/TD]
[TD="align: right"]06/12/2018 06:30[/TD]
[TD][/TD]
[TD="align: right"]02:14:52[/TD]
[/TR]
[TR]
[TD]3007[/TD]
[TD]Rhodes[/TD]
[TD="align: right"]05/12/2018 04:24[/TD]
[TD="align: right"]05/12/2018 06:00[/TD]
[TD][/TD]
[TD="align: right"]01:35:51[/TD]
[/TR]
[TR]
[TD]3007[/TD]
[TD]Rhodes[/TD]
[TD="align: right"]07/12/2018 18:03[/TD]
[TD="align: right"]07/12/2018 21:07[/TD]
[TD][/TD]
[TD="align: right"]03:04:00[/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]Slack[/TD]
[TD="align: right"]05/12/2018 02:16[/TD]
[TD="align: right"]05/12/2018 03:01[/TD]
[TD][/TD]
[TD="align: right"]00:44:47[/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]Slack[/TD]
[TD="align: right"]05/12/2018 04:28[/TD]
[TD="align: right"]05/12/2018 05:59[/TD]
[TD][/TD]
[TD="align: right"]01:30:02[/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]Slack[/TD]
[TD="align: right"]07/12/2018 18:02[/TD]
[TD="align: right"]07/12/2018 18:08[/TD]
[TD][/TD]
[TD="align: right"]00:05:35[/TD]
[/TR]
[TR]
[TD]3013[/TD]
[TD]Jagger[/TD]
[TD="align: right"]07/12/2018 15:31[/TD]
[TD="align: right"]07/12/2018 17:54[/TD]
[TD][/TD]
[TD="align: right"]02:23:01[/TD]
[/TR]
[TR]
[TD]3014[/TD]
[TD]Bowes[/TD]
[TD="align: right"]03/12/2018 03:29[/TD]
[TD="align: right"]03/12/2018 06:00[/TD]
[TD][/TD]
[TD="align: right"]02:30:13[/TD]
[/TR]
[TR]
[TD]3014[/TD]
[TD]Bowes[/TD]
[TD="align: right"]05/12/2018 18:02[/TD]
[TD="align: right"]05/12/2018 19:42[/TD]
[TD][/TD]
[TD="align: right"]01:39:30[/TD]
[/TR]
[TR]
[TD]3014[/TD]
[TD]Bowes[/TD]
[TD="align: right"]07/12/2018 03:35[/TD]
[TD="align: right"]07/12/2018 06:00[/TD]
[TD][/TD]
[TD="align: right"]02:24:58[/TD]
[/TR]
[TR]
[TD]3015[/TD]
[TD]Selways[/TD]
[TD="align: right"]04/12/2018 06:43[/TD]
[TD="align: right"]04/12/2018 07:34[/TD]
[TD][/TD]
[TD="align: right"]00:50:26[/TD]
[/TR]
[TR]
[TD]3015[/TD]
[TD]Selways[/TD]
[TD="align: right"]04/12/2018 07:34[/TD]
[TD="align: right"]04/12/2018 09:46[/TD]
[TD][/TD]
[TD="align: right"]02:11:56[/TD]
[/TR]
[TR]
[TD]3016[/TD]
[TD]Holborn[/TD]
[TD="align: right"]09/12/2018 00:41[/TD]
[TD="align: right"]09/12/2018 02:56[/TD]
[TD][/TD]
[TD="align: right"]02:14:54[/TD]
[/TR]
[TR]
[TD]3016[/TD]
[TD]Holborn[/TD]
[TD="align: right"]09/12/2018 03:21[/TD]
[TD="align: right"]09/12/2018 06:00[/TD]
[TD][/TD]
[TD="align: right"]02:38:32[/TD]
[/TR]
[TR]
[TD]3019[/TD]
[TD]Lovells[/TD]
[TD="align: right"]09/12/2018 00:42[/TD]
[TD="align: right"]09/12/2018 02:57[/TD]
[TD][/TD]
[TD="align: right"]02:15:09[/TD]
[/TR]
[TR]
[TD]3019[/TD]
[TD]Lovells[/TD]
[TD="align: right"]09/12/2018 03:20[/TD]
[TD="align: right"]09/12/2018 06:00[/TD]
[TD][/TD]
[TD="align: right"]02:39:29[/TD]
[/TR]
</tbody>[/TABLE]

 

[cpisthedbb]

Hope I'm ok to bump this post up as I've had no replies.