Morning,

I have a code that reads three columns, colour (black, blue, green, brown
and two numbers, start hours and finish hours, (1, 2, 3 etc). The code then
plots a series of coloured boxes, one colour per row to produce a gant chart.

It works fine if I enter the three input columns manually, but if I drive
the hour columns through formulas the coloured lines do not update with
changes. So for example I want to plot blue from 0 to 3 hours on row 4 and
black from 5 to 7 hours on row 5. Enter the data hit return and it works, I
get a blue bar four boxes long starting in L4 and a black one 3 boxes long
starting in P5.

Then I change the row 5 inputs to be;

start time = finish time of row 4 + 1
end time = start time + 3

Any changes to the row 4 inputs will not update the gantt, although the
values in cells driving the gantt will change.

Can anyone help me know why this happens or how to fix it?

The code I'm using is below if it helps.

Thanks
LiAD

Private Sub Worksheet_Change(ByVal Target As Range)
MyBlue = 5
MyGreen = 4
MyBrown = 18
MyBlack = 1
MyGrey = 15

MyWhite = 2

Set i = Range("I4:K20")
Set t = Target
If Intersect(t, i) Is Nothing Then Exit Sub

Application.EnableEvents = False
Select Case UCase(Cells(t.Row, "I"))
Case "BLUE": MyBack = MyBlue
MyWhite = MyBlack
Case "GREEN": MyBack = MyGreen
MyFont = MyBlack
Case "BROWN": MyBack = MyBrown
MyFont = MyBlack
Case "BLACK": MyBack = MyBlack
MyFont = MyWhite
Case "GREY": MyBack = MyGrey
MyFont = MyBlack
Case Else
Exit Sub ' color is no good
End Select

'clear old colors
Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _
..Interior.ColorIndex = xlColorIndexNone
'make font black
Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _
..Font.ColorIndex = MyBlack


StartTime = Cells(t.Row, "J")
EndTime = Cells(t.Row, "K")
If StartTime < "" And _
IsNumeric(StartTime) Then

'Start time is valid

If EndTime < "" And _
IsNumeric(EndTime) Then

'both starttime and end time are good
Range(Cells(t.Row, "L").Offset(0, StartTime), _
Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack
Range(Cells(t.Row, "L").Offset(0, StartTime), _
Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont
Else
'Start Time good end time not good
Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack
Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont
End If
Else

If EndTime < "" And _
IsNumeric(EndTime) Then

'Start time no good, end time good
Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack
Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont
Else
'start time and end time no good
End If

End If

Application.EnableEvents = True
End Sub