L
LiAD
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 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