RuneDefour
04-03-2023, 05:21 AM
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
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