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

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

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[cpisthedbb]

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

<colgroup><col><col span="3"><col span="2"></colgroup><tbody>
</tbody>

 

[cpisthedbb]

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