Hiya,

I'm trying to fix up a Work Roster created in Excel that has VB codes but I
have very very limited knowledge of VB so I am hoping someone will be able to
help me with my problem.

What's suppose to happen is when you click on a drop down menu and select an
option, like Annnual Leave. Training or Sick Leave, it's suppose to shade in
the three cells under Start, Lunch and Finish in a particular colour and also
automatically populate the time to 7 hrs 30 mins (7:30). If you select RDO
though, the hours should stay at 0.00.

However, whenever I click on the drop down and select an option, it
generates a Run-Time error 13 - Type Mismatch. When I click on debug, it
highlights the following code in yellow:

DropDownCell = Sheets("Roster").DropDowns("Drop Down " + index).LinkedCell


I have no idea on how to fix the error because I didn't create the file and
also, I think the file was created in Excel 95 or something. It works
perfectly if you run it in Excel 95 but when I run it in Excel 2003 it
doesn't work.

Can anyone please help?

I've included the rest of the VB codes below:

Sub FormatTimes(index)
Dim ColourRange As Object
Dim NumberOfHours As Object

DropDownCell = Sheets("Roster").DropDowns("Drop Down " + index).LinkedCell
ColourType = Range(DropDownCell).Value
If ColourType = 1 Then
DesiredColour = xlNone
Else
DesiredColour = Range("FirstColour").Offset(ColourType - 2,
0).Interior.ColorIndex
End If


Set ColourRange = Range(DropDownCell).Offset(0, -4).Range("A1:C1")
Set NumberOfHours = Range(DropDownCell).Offset(0, -1)
Sheets("Roster").Unprotect

Select Case ColourType
Case 2
NumberOfHours.Value = "0:00"
Case 3, 5, 6
NumberOfHours.Value = "7:30"
Case Else
NumberOfHours.FormulaR1C1 = "=RC[-1]-RC[-2]-RC[-3]"
End Select

With ColourRange.Interior
.ColorIndex = DesiredColour
.Pattern = xlSolid
End With
Sheets("ROSTER").Protect DrawingObjects:=True, Contents:=True, Scenarios _
:=True

End Sub