I'm trying to color code my schedule for work based on whether people are
closers, lates, suppers, or start before between 11:00 and 12:00 the schedule
(with color coding I'm using now - by hand) is on the net at
www.getdominos.com/staff/scedule.xls I could use conditional formatting using
=SEARCH for supper, late or close but when I tried to add an off condition,
or a condition that would catch lunches, I found I could only have 3. I treid
using VBA, but got very lost, can anyone help?
I can't quite explain this, it was passed to me when i ran out of conditional formats. It looks at a column, and based on the numeric value in the column then colours the relevant cell. It is set on my file to run on change event. This provides for ten variations, don't know if it expands further than that
Sub Line1()
For rwindex = 3 To 300
With Worksheets("Quick_LookUp").Cells(rwindex, 3)
Cells(rwindex, 2).Font.ColorIndex = 1
Cells(rwindex, 2).Font.Size = 12
Select Case .Value
Case 0: Cells(rwindex, 2).Interior.ColorIndex = 2
Case 1: Cells(rwindex, 2).Interior.ColorIndex = 36
Case 2: Cells(rwindex, 2).Interior.ColorIndex = 40
Case 3: Cells(rwindex, 2).Interior.ColorIndex = 35
Case 4: Cells(rwindex, 2).Interior.ColorIndex = 38
Case 5: Cells(rwindex, 2).Interior.ColorIndex = 34
Case 6: Cells(rwindex, 2).Interior.ColorIndex = 17
Cells(rwindex, 2).Font.ColorIndex = 2
Case 7: Cells(rwindex, 2).Interior.ColorIndex = 39
Case 8: Cells(rwindex, 2).Interior.ColorIndex = 22
Case 9: Cells(rwindex, 2).Interior.ColorIndex = 12
Cells(rwindex, 2).Font.ColorIndex = 2
End Select
End With
Next rwindex
End Sub
If you want the file i'll send it to you
Mole
Here is how I figured out how to do this.. (wasn't super hard, it was just
easier to ask in here .. I figured people could explain it pretty easy)
BUT.. is there an easier way? especially dealing with all the cases? I made
it in 5 minute intervals because that's the lowest we use, but could i
convert the 5 characters to a number and use 1100 to 1200 after somehow
converting it to a number?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Text = "off" Then
Target.Font.Italic = True
Target.Font.Bold = False
Target.Interior.ColorIndex = 0
Else
Target.Font.Bold = True
Target.Font.Italic = False
End If
If (InStr(1, Target.Text, "Supper")) Then Target.Interior.ColorIndex = 38
If (InStr(1, Target.Text, "Late")) Then Target.Interior.ColorIndex = 33
If (InStr(1, Target.Text, "Close")) Then Target.Interior.ColorIndex = 6
If (InStr(1, Target.Text, "supper")) Then Target.Interior.ColorIndex = 38
If (InStr(1, Target.Text, "late")) Then Target.Interior.ColorIndex = 33
If (InStr(1, Target.Text, "close")) Then Target.Interior.ColorIndex = 6
Select Case (Left$(Target.Text, 5))
Case "10:00"
Target.Interior.ColorIndex = 4
Case "11:00"
Target.Interior.ColorIndex = 4
Case "11:05"
Target.Interior.ColorIndex = 4
Case "11:10"
Target.Interior.ColorIndex = 4
Case "11:15"
Target.Interior.ColorIndex = 4
Case "11:20"
Target.Interior.ColorIndex = 4
Case "11:25"
Target.Interior.ColorIndex = 4
Case "11:30"
Target.Interior.ColorIndex = 4
Case "11:35"
Target.Interior.ColorIndex = 4
Case "11:40"
Target.Interior.ColorIndex = 4
Case "11:45"
Target.Interior.ColorIndex = 4
Case "11:50"
Target.Interior.ColorIndex = 4
Case "11:55"
Target.Interior.ColorIndex = 4
Case "12:00"
Target.Interior.ColorIndex = 4
End Select
End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks