Hi,

I recieved this code from Joel which works well when numbers are typed in
and formatted as numbers. However when I try to change Cols J and K to
formulas the code does not work. Even if the numbers change the coloured
bars remain the same. If I try to overwrite the formulas with the numbers I
want it still does not work. The formulas are simple K4=J4+G4, K4+1=J5 etc.
entered ino excel not VB.

In a similar way if I write e instead of 2 for example the code defaults,
which is normal, but then after I cannot reset it or get it to work again,
(the reason that might happen is because I'm using a French laptop which u
need to use caps lock on the get the numbers so if u forget u get weird
letters instead).

There is also something funny in the format of the numbers in Col K which
gets changed to white automatically sometimes and hence disappears.

Is it possible someone could give me some hints on how to get rid of these
issues please?

Thanks
---------------------

Private Sub Worksheet_Change(ByVal Target As Range)
MyBlue = 5
MyGreen = 10
MyBrown = 9
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 = MyWhite


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

I'm sorry about the white color. I made the code flexible that you could set
the font as well as the background color because you wouldn't be able to see
black fonts on a blach background. Simply comment out the following line of
code

MyFont = MyWhite

I'm not sure why formulas aren't working. Add a message box in this code to
help find the problem

StartTime = Cells(t.Row, "J")
EndTime = Cells(t.Row, "K")
msgbox("StartTime : " & StartTime & "; EndTime : " & EndTime)


You can also try using Value like below
StartTime = Cells(t.Row, "J").Value
EndTime = Cells(t.Row, "K").Value
msgbox("StartTime : " & StartTime & "; EndTime : " & EndTime)



"LiAD" wrote:

Hi,

I recieved this code from Joel which works well when numbers are typed in
and formatted as numbers. However when I try to change Cols J and K to
formulas the code does not work. Even if the numbers change the coloured
bars remain the same. If I try to overwrite the formulas with the numbers I
want it still does not work. The formulas are simple K4=J4+G4, K4+1=J5 etc.
entered ino excel not VB.

In a similar way if I write e instead of 2 for example the code defaults,
which is normal, but then after I cannot reset it or get it to work again,
(the reason that might happen is because I'm using a French laptop which u
need to use caps lock on the get the numbers so if u forget u get weird
letters instead).

There is also something funny in the format of the numbers in Col K which
gets changed to white automatically sometimes and hence disappears.

Is it possible someone could give me some hints on how to get rid of these
issues please?

Thanks
---------------------

Private Sub Worksheet_Change(ByVal Target As Range)
MyBlue = 5
MyGreen = 10
MyBrown = 9
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 = MyWhite


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