| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ColorIndex and Caps Change too slow
In a scheduling speadsheet I have 50 rows of employees and 365 columns
of days. After making an entry into each cell, I want to verify that the entry is one of 40 approved codes, display it in all caps, color the interior and font according to a dynamic legend that I create somewhere on the sheet (or different sheet). When I initially started this project, my color and font tests worked very well, but I have found that it gets very slow as I expanded to full range size (especially when doing the caps change line). If I can, I want to create a legend that shows what the different codes, interior shading, font colors are, and the sub will use it to do its error checking and shading. I copied much of this code from another site, but it got too slow as I added more of my needs. There is bound to be a much smarter way to get this project rolling. Please set me on a better path. Here is what I have so far: Private Sub Worksheet_Change(ByVal Target As Range) Set rng = Range("c7:dj52") For Each cl In rng cl.Value = UCase(cl.Value) If cl.Value = "AL" Then cl.Cells.Interior.ColorIndex = 3 ElseIf cl.Value = "SL" Then cl.Cells.Interior.ColorIndex = 3 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = "FL" Then cl.Cells.Interior.ColorIndex = 3 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = "ML" Then cl.Cells.Interior.ColorIndex = 3 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = "DL" Then cl.Cells.Interior.ColorIndex = 3 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = "WL" Then cl.Cells.Interior.ColorIndex = 3 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = "OL" Then cl.Cells.Interior.ColorIndex = 3 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = "CL" Then cl.Cells.Interior.ColorIndex = 3 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = "PL" Then cl.Cells.Interior.ColorIndex = 3 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = "JD" Then cl.Cells.Interior.ColorIndex = 3 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = "X" Then cl.Cells.Interior.ColorIndex = 15 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = "HO" Then cl.Cells.Interior.ColorIndex = 15 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = "00" Then cl.Cells.Interior.ColorIndex = 20 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = "01" Then cl.Cells.Interior.ColorIndex = 20 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = "02" Then cl.Cells.Interior.ColorIndex = 20 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = "03" Then cl.Cells.Interior.ColorIndex = 20 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = "04" Then cl.Cells.Interior.ColorIndex = 19 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = "05" Then cl.Cells.Interior.ColorIndex = 19 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = "06" Then cl.Cells.Interior.ColorIndex = 19 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = "07" Then cl.Cells.Interior.ColorIndex = 19 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = "08" Then cl.Cells.Interior.ColorIndex = 19 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = "09" Then cl.Cells.Interior.ColorIndex = 19 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = 10 Then cl.Cells.Interior.ColorIndex = 19 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = 11 Then cl.Cells.Interior.ColorIndex = 19 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = 12 Then cl.Cells.Interior.ColorIndex = 17 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = 13 Then cl.Cells.Interior.ColorIndex = 17 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = 14 Then cl.Cells.Interior.ColorIndex = 17 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = 15 Then cl.Cells.Interior.ColorIndex = 17 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = 16 Then cl.Cells.Interior.ColorIndex = 17 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = 17 Then cl.Cells.Interior.ColorIndex = 17 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = 18 Then cl.Cells.Interior.ColorIndex = 17 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = 19 Then cl.Cells.Interior.ColorIndex = 20 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = 20 Then cl.Cells.Interior.ColorIndex = 20 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = 21 Then cl.Cells.Interior.ColorIndex = 20 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = 22 Then cl.Cells.Interior.ColorIndex = 20 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = 23 Then cl.Cells.Interior.ColorIndex = 20 cl.Cells.Font.ColorIndex = 1 ElseIf cl.Value = "HO" Then cl.Cells.Interior.ColorIndex = 15 cl.Cells.Font.ColorIndex = 2 ElseIf cl.Value = "T" Then cl.Cells.Interior.ColorIndex = 4 cl.Cells.Font.ColorIndex = 3 ElseIf cl.Value = "<T" Then cl.Cells.Interior.ColorIndex = 4 cl.Cells.Font.ColorIndex = 3 ElseIf cl.Value = "OP" Then cl.Cells.Interior.ColorIndex = 4 cl.Cells.Font.ColorIndex = 3 ElseIf cl.Value = "TR" Then cl.Cells.Interior.ColorIndex = 4 cl.Cells.Font.ColorIndex = 3 ElseIf cl.Value = "AD" Then cl.Cells.Interior.ColorIndex = 4 cl.Cells.Font.ColorIndex = 3 ElseIf cl.Value = "MS" Then cl.Cells.Interior.ColorIndex = 4 cl.Cells.Font.ColorIndex = 3 ElseIf cl.Value = "TD" Then cl.Cells.Interior.ColorIndex = 4 cl.Cells.Font.ColorIndex = 3 ElseIf cl.Value = "Null" Then cl.Cells.Interior.ColorIndex = 16 cl.Cells.Font.ColorIndex = 1 Else cl.Cells.Interior.ColorIndex = 0 cl.Cells.Font.ColorIndex = 1 End If Next End Sub |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| In excel can you change words from all caps to initial caps? | Excel Discussion (Misc queries) | |||
| Trying to change ColorIndex for series settings | Charts and Charting in Excel | |||
| How to change ALL CAPS to Initial Caps only? | Excel Worksheet Functions | |||
| change font of certain cells in row(s) when ColorIndex of Column S changes | Excel Programming | |||
| Is ther a way to change the interior.colorindex of multiple rows | Excel Programming | |||
Threaded Mode
