Format Code - Joel




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?


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
'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

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
'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

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)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

RunTime code problem 4
Loading Excel Array from VB Array Faster 3
Macro Help Please 1
Excel With Outlook 1
Solution for conditional formatting issue 2
Worksheet Event Code 2
DateDiff Problem 2
Formatting via VBA 9