Speed up Color Changing

[tmo4ever]

Can anyone help with this?

I have a Gantt chart that I'm building. Since I need to have 4 color options, I can't use conditional formatting, so I'm using a great script that erik.van.geit came up with.

Is there a quicker way to have to code only make changes to each row rather than the whole range? It can take several seconds to update because of the number of cells.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim cell As Range
Dim colors As Variant
Set WatchRange = Range("k7:ip40")
colors = Array("2", "36", "50", "41", "3")

For Each cell In WatchRange
  If cell <> "" And IsNumeric(cell) Then
  cell.Interior.ColorIndex = colors(cell.Value)
  If cell.Value <> 0 Then cell.Font.ColorIndex = colors(cell.Value)
  End If
Next cell
End Sub

Any help would be great!

Thanks!

 

[tmo4ever]

In row 2 of the sample you sent, is there any reason not to date dates by day seeded there?

No, that was just the nest way I knew how to set it up so the weekends wouldn't be calculated.