How do I color code a worksheet based on text
 

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


--
Maurice


------------------------------------------------------------------------
Maurice's Profile: http://www.excelforum.com/member.php...fo&userid=1948
View this thread: http://www.excelforum.com/showthread...hreadid=374973

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