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.
Any help would be great!
Thanks!
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.
Code:
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!