Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort or Filter option? | Excel Worksheet Functions | |||
How to auto-fill text based on text in another cell | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Putting text in a column based on variable text from another colum | Excel Discussion (Misc queries) | |||
returning a text cell based on a number cell | Excel Worksheet Functions |