Hi
First post, so please excuse if added items/references wrong.
I am trying to develop a Gantt using VBA, I cannot use the Conditional Fromatting style or any style that prevents me from referencing the date cells which have colour or text.
I would like to have two sheets. Sheet 1 = Gantt and sheet 2 will have a lookup for specific engineer.
My initial thought is to create a gantt ffrom which I can Index match (or similar, perhaps even use VBA again) and populate the date ranges booked in sheet one against each engineers name in sheet 2
Code I have used so far seems to hang Excel, but if I add "Stop" before "End Sub " it does both colour and add text.
Any assistance would be appreciated.
CODE:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j As Integer
ScreenUpdating = False
On Error Resume Next
FirstRow = Sheets("Sheet1").Usedrange.Rows(2).Row
LastRow = Sheets("Sheet1").Usedrange.Rows(ActiveSheet.Usedrange.Rows.Count).Row
LastCol = Sheets("Sheet1").Usedrange.Columns(ActiveSheet.Usedrange.Columns.Count).Column
With Sheets("Sheet1")
For j = 5 To LastCol
For i = 2 To LastRow
If Sheets("Sheet1").Application.WorksheetFunction.IsText(Cells(i, 1)) = True And Cells(1, j).Value >= Cells(i, 3).Value And Cells(1, j).Value <= Cells(i, 4).Value Then
Sheets("Sheet1").Usedrange.Cells(i, j).Value = Cells(i, 2)
Sheets("Sheet1").Usedrange.Cells(i, j).Interior.ColorIndex = 4
End If
Next
Next
End With
ScreenUpdating = True
End Sub
Sheet 2
[TABLE="class: grid, width: 1250, align: left"]
<tbody>[TR]
[TD="width: 148"]ENGINEER[/TD]
[TD="width: 75"]21/01/2017[/TD]
[TD="width: 75"]22/01/2017[/TD]
[TD="width: 75"]23/01/2017[/TD]
[TD="width: 75"]24/01/2017[/TD]
[TD="width: 75"]25/01/2017[/TD]
[TD="width: 75"]26/01/2017[/TD]
[TD="width: 75"]27/01/2017[/TD]
[TD="width: 75"]28/01/2017[/TD]
[TD="width: 75"]29/01/2017[/TD]
[TD="width: 75"]30/01/2017[/TD]
[TD="width: 75"]31/01/2017[/TD]
[TD="width: 75"]01/02/2017[/TD]
[TD="width: 75"]02/02/2017[/TD]
[/TR]
[TR]
[TD]Engineer 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AD[/TD]
[TD]AD[/TD]
[TD]AD[/TD]
[TD][/TD]
[TD][/TD]
[TD]WD[/TD]
[TD]WD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 2[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 3[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 4[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 5[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 6[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 7[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet 1
[TABLE="class: grid, width: 1500, align: left"]
<tbody>[TR]
[TD="width: 148"]ENGINEER[/TD]
[TD="width: 148"]PROJECT[/TD]
[TD="width: 148"]START DATE[/TD]
[TD="width: 148"]END DATE[/TD]
[TD="width: 75"]21/01/2017[/TD]
[TD="width: 75"]22/01/2017[/TD]
[TD="width: 75"]23/01/2017[/TD]
[TD="width: 75"]24/01/2017[/TD]
[TD="width: 75"]25/01/2017[/TD]
[TD="width: 75"]26/01/2017[/TD]
[TD="width: 75"]27/01/2017[/TD]
[TD="width: 75"]28/01/2017[/TD]
[TD="width: 75"]29/01/2017[/TD]
[TD="width: 75"]30/01/2017[/TD]
[TD="width: 75"]31/01/2017[/TD]
[TD="width: 75"]01/02/2017[/TD]
[TD="width: 75"]02/02/2017[/TD]
[/TR]
[TR]
[TD]Engineer 1[/TD]
[TD]WD[/TD]
[TD]24/01/2017[/TD]
[TD]26/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AD[/TD]
[TD]AD[/TD]
[TD]AD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 1[/TD]
[TD]WD[/TD]
[TD]25/01/2017[/TD]
[TD]26/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WD[/TD]
[TD]WD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 2[/TD]
[TD]A3[/TD]
[TD]21/01/2017[/TD]
[TD]27/01/2017[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 3[/TD]
[TD]A4[/TD]
[TD]21/01/2017[/TD]
[TD]28/01/2017[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 4[/TD]
[TD]A5[/TD]
[TD]21/01/2017[/TD]
[TD]29/01/2017[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 5[/TD]
[TD]A6[/TD]
[TD]21/01/2017[/TD]
[TD]30/01/2017[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 6[/TD]
[TD]A7[/TD]
[TD]21/01/2017[/TD]
[TD]31/01/2017[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 7[/TD]
[TD]A9[/TD]
[TD]21/01/2017[/TD]
[TD]01/02/2017[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
First post, so please excuse if added items/references wrong.
I am trying to develop a Gantt using VBA, I cannot use the Conditional Fromatting style or any style that prevents me from referencing the date cells which have colour or text.
I would like to have two sheets. Sheet 1 = Gantt and sheet 2 will have a lookup for specific engineer.
My initial thought is to create a gantt ffrom which I can Index match (or similar, perhaps even use VBA again) and populate the date ranges booked in sheet one against each engineers name in sheet 2
Code I have used so far seems to hang Excel, but if I add "Stop" before "End Sub " it does both colour and add text.
Any assistance would be appreciated.
CODE:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j As Integer
ScreenUpdating = False
On Error Resume Next
FirstRow = Sheets("Sheet1").Usedrange.Rows(2).Row
LastRow = Sheets("Sheet1").Usedrange.Rows(ActiveSheet.Usedrange.Rows.Count).Row
LastCol = Sheets("Sheet1").Usedrange.Columns(ActiveSheet.Usedrange.Columns.Count).Column
With Sheets("Sheet1")
For j = 5 To LastCol
For i = 2 To LastRow
If Sheets("Sheet1").Application.WorksheetFunction.IsText(Cells(i, 1)) = True And Cells(1, j).Value >= Cells(i, 3).Value And Cells(1, j).Value <= Cells(i, 4).Value Then
Sheets("Sheet1").Usedrange.Cells(i, j).Value = Cells(i, 2)
Sheets("Sheet1").Usedrange.Cells(i, j).Interior.ColorIndex = 4
End If
Next
Next
End With
ScreenUpdating = True
End Sub
Sheet 2
[TABLE="class: grid, width: 1250, align: left"]
<tbody>[TR]
[TD="width: 148"]ENGINEER[/TD]
[TD="width: 75"]21/01/2017[/TD]
[TD="width: 75"]22/01/2017[/TD]
[TD="width: 75"]23/01/2017[/TD]
[TD="width: 75"]24/01/2017[/TD]
[TD="width: 75"]25/01/2017[/TD]
[TD="width: 75"]26/01/2017[/TD]
[TD="width: 75"]27/01/2017[/TD]
[TD="width: 75"]28/01/2017[/TD]
[TD="width: 75"]29/01/2017[/TD]
[TD="width: 75"]30/01/2017[/TD]
[TD="width: 75"]31/01/2017[/TD]
[TD="width: 75"]01/02/2017[/TD]
[TD="width: 75"]02/02/2017[/TD]
[/TR]
[TR]
[TD]Engineer 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AD[/TD]
[TD]AD[/TD]
[TD]AD[/TD]
[TD][/TD]
[TD][/TD]
[TD]WD[/TD]
[TD]WD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 2[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 3[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 4[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 5[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 6[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 7[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet 1
[TABLE="class: grid, width: 1500, align: left"]
<tbody>[TR]
[TD="width: 148"]ENGINEER[/TD]
[TD="width: 148"]PROJECT[/TD]
[TD="width: 148"]START DATE[/TD]
[TD="width: 148"]END DATE[/TD]
[TD="width: 75"]21/01/2017[/TD]
[TD="width: 75"]22/01/2017[/TD]
[TD="width: 75"]23/01/2017[/TD]
[TD="width: 75"]24/01/2017[/TD]
[TD="width: 75"]25/01/2017[/TD]
[TD="width: 75"]26/01/2017[/TD]
[TD="width: 75"]27/01/2017[/TD]
[TD="width: 75"]28/01/2017[/TD]
[TD="width: 75"]29/01/2017[/TD]
[TD="width: 75"]30/01/2017[/TD]
[TD="width: 75"]31/01/2017[/TD]
[TD="width: 75"]01/02/2017[/TD]
[TD="width: 75"]02/02/2017[/TD]
[/TR]
[TR]
[TD]Engineer 1[/TD]
[TD]WD[/TD]
[TD]24/01/2017[/TD]
[TD]26/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AD[/TD]
[TD]AD[/TD]
[TD]AD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 1[/TD]
[TD]WD[/TD]
[TD]25/01/2017[/TD]
[TD]26/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WD[/TD]
[TD]WD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 2[/TD]
[TD]A3[/TD]
[TD]21/01/2017[/TD]
[TD]27/01/2017[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD]A3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 3[/TD]
[TD]A4[/TD]
[TD]21/01/2017[/TD]
[TD]28/01/2017[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD]A4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 4[/TD]
[TD]A5[/TD]
[TD]21/01/2017[/TD]
[TD]29/01/2017[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD]A5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 5[/TD]
[TD]A6[/TD]
[TD]21/01/2017[/TD]
[TD]30/01/2017[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD]A6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 6[/TD]
[TD]A7[/TD]
[TD]21/01/2017[/TD]
[TD]31/01/2017[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD]A7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Engineer 7[/TD]
[TD]A9[/TD]
[TD]21/01/2017[/TD]
[TD]01/02/2017[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD]A9[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]