problem adding 2 values ​​in 2 columns

[RuneDefour]

everything runs smoothly except at step. adding 2 the value in column 5 and the value in column 7. the result is not correct and the whole program crashes and exits. what's my mistake? the bold is where it goes wrong

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastRow As Long
Dim i As Long
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row 
For i = 11 To lastRow 
   If Cells(i, 8) = 0 Then 
      If Cells(i, 7) < 1 And Cells(i, 5) < Cells(i, 6) Then 
         Cells(i, 5).Interior.Color = RGB(255, 153, 153) 
         ElseIf Cells(i, 5) >= Cells(i, 6) Then 
         Cells(i, 5).Interior.Color = RGB(255, 255, 255) 
         ElseIf Cells(i, 7) >= 1 Then 
         Cells(i, 5).Interior.Color = RGB(255, 204, 153) 
      End If
   ElseIf Cells(i, 8) >= 1 Then 
   Cells(i, 5).Value = Cells(i, 5).Value + Cells(i, 7).Value 
   Cells(i, 7).Value = 0 
   Cells(i, 5).Interior.ColorIndex = xlColorIndexNone 
   End If
    Next i
End Sub

[arnelgp]

can you just Create a Conditional Formatting on the Cells, instead of using VBA code.

Private Sub Worksheet_Change(ByVal Target As Range)Dim lastRow As Long
Dim i As Long
Application.EnableEvents = False
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 11 To lastRow
    If Val(Cells(i, 8) & "") = 0 Then
        If Val(Cells(i, 7) & "") < 1 And Val(Cells(i, 5) & "") < Val(Cells(i, 6) & "") Then
            Cells(i, 5).Interior.Color = RGB(255, 153, 153)
        ElseIf Val(Cells(i, 5) & "") >= Val(Cells(i, 6) & "") Then
            Cells(i, 5).Interior.Color = RGB(255, 255, 255)
        ElseIf Val(Cells(i, 7) & "") >= 1 Then
            Cells(i, 5).Interior.Color = RGB(255, 204, 153)
        End If
    ElseIf Val(Cells(i, 8) & "") >= 1 Then
        Cells(i, 5) = Val(Cells(i, 5) & "") + Val(Cells(i, 7) & "")
        Cells(i, 7) = 0
        Cells(i, 5).Interior.ColorIndex = xlColorIndexNone
    End If
Next i
Application.EnableEvents = True
End Sub

[Paul_Hossler]

Couple of observations

You don't use Target so every change triggers the event handler

You don't turn off EnableEvents inside so it keeps calling itself until Excel gives up

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastRow As Long
Dim i As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.EnableEvents = False
For i = 11 To lastRow
    If Cells(i, 8) = 0 Then
       If Cells(i, 7) < 1 And Cells(i, 5) < Cells(i, 6) Then
          Cells(i, 5).Interior.Color = RGB(255, 153, 153)
       ElseIf Cells(i, 5) >= Cells(i, 6) Then
           Cells(i, 5).Interior.Color = RGB(255, 255, 255)
       ElseIf Cells(i, 7) >= 1 Then
           Cells(i, 5).Interior.Color = RGB(255, 204, 153)
       End If
   ElseIf Cells(i, 8) >= 1 Then
       Cells(i, 5).Value = Cells(i, 5).Value + Cells(i, 7).Value
       Cells(i, 7).Value = 0
       Cells(i, 5).Interior.ColorIndex = xlColorIndexNone
   End If
Next i
Application.EnableEvents = True
End Sub