Yes the formula you quoted is what I have.
Yes the worksheet is on auto calculate.

In J4 I have the number 0 formatted as a number in G4 is another number
which I'm changing to try to see if it works, between 1 and 5.

Tried hour and it doesn't change anything for me here.

I assume you are talking about the double change, once for when I change G4
and once for when the formula automatically changes K4 due to the change made
in G4? Why does the code need to worry about G4, can it just not be driven
off the output generated in K4?


"joel" wrote:

I didn't say that that it is not possible!!!!!!!

You have the following in cell K4

= J4 + G4

When G4 change so does K4. The worksheet change function gets call twice.
the first time when you change G4 and the second time after the worksheet is
re-calculated and K4 changes. You don't need to have the macro run when G4
changes. The line "If Not Intersect(t, i) Is Nothing Then" stops the code
from running when G4 changes put allows the code to run when K4 changes.

Do you have the worksheet set to Recaculate Automatically???

The 2nd time the function is called after K4 is changed the Gant chart
should update. You also may need to put HOUR() back into the code. You
never posted what data was in G4 and J4.



"LiAD" wrote:

Is it not possible that I can use G4 to update K4 which will then change the
gannt?

For example in G5 I have 2, J5 I have =K4, K5 I have = J5+G5. For now we
can imagine that the gannt shows a coloured line 3 cells long.

Now I change G5 from 2 to 4 and hit enter and the gannt will automatically
change from 3 to 5 cells long.

Is this possible?

Thanks


"joel" wrote:

the code is working the way I intended it to work. Since changing G4 doesn't
directly change the gant chart nothing will happen. The code loops 1 time
for each cell that gets changed on the worksheet. When you change G4 the
cell K4 will also change and the code will loop a 2nd time for K4. When K4 =
target (the 2nd time though the loop) the code should update the gant chart.

"LiAD" wrote:

Hi,

With the new code and the formulas in cols J and K, fed by Col G the code
stops at this line:

If Not Intersect(t, i) Is Nothing Then

Would you have any more ideas?
Thanks a lor for your help

"joel" wrote:

I forgot that line was in the code. Now it should loop. The problem was G4
was no in the intesect area and was causing the macro to stop. the code
wilnow continue after G4.

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

MyWhite = 2

Application.EnableEvents = False

Set i = Range("I4:K20")
For Each t In Target

If Not Intersect(t, i) Is Nothing Then

BadColor = 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
' color is no good
BadColor = True
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 BadColor = False Then
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
End If
End If
Next t
Application.EnableEvents = True
End Sub




"LiAD" wrote:

Hi,

I have found that with formulas the code stops after

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

So when I change a cell the code exits almost immediately.

Interesting if I copy the formula down one cell at a time after a change it
updates. If I copy then change or copy more than one cell at a time it
doesnt work.

Does this help at all?

I think I owe you serious beer after this one!

"joel" wrote:

I added spaces at the beginning of the line which cuased the end of the line
to wrap

from
Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex =
MyBack

to (aaded line continuation character - an underline)

Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = _
MyBack


"LiAD" wrote:

With this code I'm finding an issue on the following line -:

Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex =

It gives a compile error: Syntax error.

"joel" wrote:

I asumed you put the message box at the 1st line of the macro. if you are
not seeing the message box either there is something wrong with the data or
another macro is causing an error. I would need to see the data to determine
what the probelm is.

You could add a break point on the worksheet change line in your macro.
click on this line with mouse and press F9. the line should change color.
First make sure you get to the break point when the macro is working. Press
F5 to continue after getting to the break point. Doing this will tell us if
the data is bad (gets to this line) or something else in the workbook is
failing (doesn't get to the line).

The hour will has nothing to do with the function being called. After we
find out why the macro isn't runni9ng then we have to address the hour issue.
I would need to see samples of the data to determine if the hour is needed.
It is only needed if you have a cell formated as time.

You also need to change the function to add a loop since yo are changing
more than one value in the range. When you change J4 you are also changing
K4 so the function need to loop. Exscel does all the calculattions on the
worksheet before calling the worksheet change function.

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

MyWhite = 2

Application.EnableEvents = False

Set i = Range("I4:K20")
For Each t In Target

If Intersect(t, i) Is Nothing Then Exit Sub

BadColor = 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
' color is no good
BadColor = True
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 BadColor = False Then
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
End If
Next t
Application.EnableEvents = True
End Sub


"LiAD" wrote:

Yes correct. I see no message box.