Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Multiple Cell Colours based on Drop Down option Selected
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing drop down option after its been selected | Excel Worksheet Functions | |||
changing how font is selected from drop down menu | Excel Discussion (Misc queries) | |||
Format drop down option won't open on selected cells | Excel Discussion (Misc queries) | |||
Option Button - multiple selected | Excel Worksheet Functions | |||
multiple or changing colours in a data table on an excel chart fo. | Charts and Charting in Excel |