Slow macro wasting time

[redmanYJ]

I'm trying to run code to color in some cells in a row based on the value they contain. The following code works but it takes too much time to recalculate all 106,500 cells every time you change a value. I need to have it run through a worksheet change event. Is there anyway to have it only recalculate the cell range in the row that I'm changing instead of all 740 rows each time I change a cell?
eg. If I change G20, it would only need it to recalculate N20:EY20. Or if I change F234, it would only recalculate N234:EY234 etc.

Dim spot As Range
For Each spot In Range("N10:EY750")
spot.Interior.ColorIndex = spot.Value
Next

 

[VoG]

Hello and welcome to MrExcel.

Try this. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim spot As Range
For Each spot In Range("N" & Target.Row & ":EY" & Target.Row)
    spot.Interior.ColorIndex = spot.Value
Next spot
End Sub

 

[redmanYJ]

Thanks, works great. Now I need to exclude rows 1:9 from being changed by the event.

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim spot As Range
If Target.Row < 10 Then Exit Sub
For Each spot In Range("N" & Target.Row & ":EY" & Target.Row)
    spot.Interior.ColorIndex = spot.Value
Next spot
End Sub

 

[redmanYJ]

Perfect! Thank You!